<?php
namespace app\index\controller;
use app\index\model\CrmCourseDuration;
use app\index\model\CrmCustomerManage;
use app\index\model\CrmCustomerYycourse;
use app\index\model\CrmCustomerZhuanyi;
use app\index\model\CrmDurations;
use app\index\model\CrmFenzu;
use app\index\model\CrmManage;
use app\index\model\CrmMedia;
use app\index\model\Goods;
use app\index\model\GoodsNext;
use app\index\model\CrmMediaNext;
use app\index\model\CrmCustomer;
use think\Db;

class Count extends Base
{
    // 渠道进线统计
    public function sourceTypeList()
    {
//        $data = input();//token
//        $list = [];
//        for ($i=1; $i < 100; $i++) {
//            $list[] = array('id'=>$i,'name'=>'超强记忆训练营'.$i);
//        }
//        $list[] = array('id'=>101,'name'=>'系统班 type101');
//        for ($i=102; $i < 123; $i++) {
//            $list[] = array('id'=>$i,'name'=>'超强记忆训练营'.$i);
//        }
//        for ($i=124; $i < 200; $i++) {
//            $list[] = array('id'=>$i,'name'=>'超强记忆训练营'.$i);
//        }
//        $list[] = array('id'=>200,'name'=>'9.9 type200');
//        $list[] = array('id'=>201,'name'=>'9.9 type201');
//        $list[] = array('id'=>202,'name'=>'9.9 type202');
//        $list[] = array('id'=>203,'name'=>'9.9 type203');
//        $list[] = array('id'=>204,'name'=>'9.9 type204');
//        $list[] = array('id'=>205,'name'=>'9.9 type205');
//
//        $list[] = array('id'=>301,'name'=>'移动官网');
//        $list[] = array('id'=>211,'name'=>'69 type211');
//        $list[] = array('id'=>123,'name'=>'小低 type123');
//        $list[] = array('id'=>456,'name'=>'小高 type456');
//        $list[] = array('id'=>457,'name'=>'超强记忆系统班 type457');
//        $list[] = array('id'=>210,'name'=>'思维 type210');
//        $list[] = array('id'=>459,'name'=>'思维小低 type459');
//        $list[] = array('id'=>460,'name'=>'超强记忆系统班新 type460');
//        $list[] = array('id'=>461,'name'=>'超强记忆系统班新 type461');
//        $list[] = array('id'=>462,'name'=>'2980单独 type462');
        $list = (new CrmMedia())->where('state',1)->field('id,media as name')->select();
        apiReturn(200,'获取成功',$list);
    }



    /*
     *渠道数据报表
     *  */
    public function sourceCount1()
    {
        $data = input();//token starttime endtime日期 zu_id部门 media推广渠道id source_type链接来源 course_id营期课程

        // $data['starttime'] = "2020-04-01 00:00:00";
        // $data['endtime'] = "2020-04-30 00:00:00";
        $time = '';
        if (isset($data['starttime']) && isset($data['endtime'])) {
            if (!empty($data['starttime']) && !empty($data['endtime'])) {
                $where['date'] = array(array('egt',strtotime($data['starttime'])),array('elt',strtotime($data['endtime'])));
                if (substr($data['starttime'], 0,10) == substr($data['endtime'], 0,10)) {
                    $time = substr($data['starttime'], 0,10);
                }else{
                    $time = substr($data['starttime'], 0,10).'-'.substr($data['endtime'], 0,10);
                }
            }else{
                apiReturn(201,'时间不能为空');
            }
        }else{
            apiReturn(201,'时间不能为空');
        }
        if (isset($data['zu_id'])) {
            if (!empty($data['zu_id'])) {
                $wherezu_id = $this->getFenzuList($data['zu_id']);
                $where['manage_id'] = $this->getManageList($wherezu_id);
            }
        }
        if (isset($data['media'])) {
            if (!empty($data['media'])) {
                $where1['id'] = $data['media'];
            }
        }
        if (isset($data['source_type'])) {
            if (!empty($data['source_type'])) {
                $where['source_type'] = $data['source_type'];
            }
        }
        if (isset($data['source_type'])) {
            if (!empty($data['source_type'])) {
                $where['source_type'] = $data['source_type'];
            }
        }
        if (isset($data['course_id'])) {
            if (!empty($data['course_id'])) {
                $where['public_yingqi'] = $data['course_id'];
            }
        }
        $where1['state'] = 1;
        
        //推广来源列表
        $sourceList = db('crm_media')->field('id,media,phone')->where($where1)->order('id ASC')->select();
        foreach ($sourceList as $key => $value) {
            $jinxian = 0;//进线数
            $tuikuan = 0;//退款数
            $tuikuan_rate = 0;//退款率
            $baoke = 0;//报课数
            $zhuanhua = 0;//转化率
            $danfenjia = 0;//单粉价

            $where['source'] = $value['media'];
            $jinxian = db('crm_customer')->where($where)->count();
            $where2 = $where;
            $where2['is_refund'] = 1;
            $tuikuan = db('crm_customer')->where($where2)->count();
            if (!empty($jinxian)) {
                $tuikuan_rate = sprintf("%.2f",$tuikuan/$jinxian);
            }else{
                $tuikuan_rate = 0;
            }
            $baoke = db('crm_customer')->where(array('source'=>$value['media'],'public_paytype'=>array('in','2,3')))->count();
            if (!empty($jinxian)) {
                $zhuanhua = sprintf("%.2f",$baoke/$jinxian);
            }else{
                $zhuanhua = 0;
            }
            $total = db('crm_customer')->where(array('source'=>$value['media']))->sum('total');
            if (!empty($jinxian)) {
                $danfenjia = sprintf("%.2f",$total/$jinxian);
            }else{
                $danfenjia = 0;
            }
            $sourceList[$key]['time'] = $time;
            $sourceList[$key]['jinxian'] = $jinxian;
            $sourceList[$key]['tuikuan'] = $tuikuan;
            $sourceList[$key]['tuikuan_rate'] = $tuikuan_rate;
            $sourceList[$key]['baoke'] = $baoke;
            $sourceList[$key]['zhuanhua'] = $zhuanhua;
            $sourceList[$key]['danfenjia'] = $danfenjia;
        }

        apiReturn(200,'渠道数据报表',$sourceList);
    }


    public function sourceCount2(){
        $query = input();
        $query['limit'] = isset($query['limit'])?$query['limit']:10;
        $query['page'] = isset($query['page'])?$query['page']:1;
        if(isset($query['endtime']) && isset($query['starttime'])){
            if($query['starttime']>$query['endtime'])apiReturn(400,'查询开始日期不能大于结束日期');
        }
        if(empty($query['starttime'])|| empty($query['endtime'])){
            $query['starttime'] = date('Y-m-d H:i:s',mktime(0, 0, 0, date('m'), date('d'), date('Y'))) ;
            $query['endtime'] =date('Y-m-d H:i:s',mktime(23, 59, 59, date('m'), date('d'), date('Y'))) ;
        }
        $this->channel($query);
    }

    public function channel_chain(){
        $query = input();
        $query['limit'] = isset($query['limit'])?$query['limit']:10;
        $query['page'] = isset($query['page'])?$query['page']:1;
        if(empty($query['starttime'])|| empty($query['endtime'])){
            $query['starttime'] = date('Y-m-d H:i:s',mktime(0, 0, 0, date('m'), date('d'), date('Y'))) ;
            $query['endtime'] =date('Y-m-d H:i:s',mktime(23, 59, 59, date('m'), date('d'), date('Y'))) ;
        }
        $this->channel($query);
    }



    private function channel($query){
        $source_type_where=[];
        if(!empty($query['id'])){
            $source_type_arr = (new CrmMediaNext())->where('media_id',$query['id'])->column('type');
            foreach ($source_type_arr as $k=>$v){
                if(is_int($v)){
                    $source_type_arr[$k]=(string)$v;
                }
            }
            $source_type_where['c.source_type']=['in',$source_type_arr];
        }
        $CrmCustomerM = new CrmCustomer;
        $CrmMediaM = new CrmMedia;
        $source_type_group = $CrmCustomerM->alias('c')->join('crm_media_next n','c.source_type=n.type')
            ->where($source_type_where)->where('c.date','between',[strtotime($query['starttime']),strtotime($query['endtime'])])
            ->group('c.source_type')->limit($query['limit'])->page($query['page'])
            ->field('n.media_id,c.source_type,count(*) as amount')->order('amount desc')->select();
        $totals = $CrmCustomerM->alias('c')->where($source_type_where)->where('c.date','between',[strtotime($query['starttime']),strtotime($query['endtime'])])->group('c.source_type')->count();
        $data['total_amount']=0;
        $data['total_refund_amount']=0;
        $data['total_refund_rate']='0%';
        $data['total_conversion'] = 0;
        $data['wuxiao_amount'] =0;
        $data['youxiao_amount'] = 0;
        $data['wuxiao_rate']='0%';
        $data['youxiao_rate']='0%';
        unset($query['id']);
        foreach ($source_type_group as &$value){
            $value['media_name']='无添加渠道';
            if(!empty($value['media_id'])){
                $value['media_name']=$CrmMediaM->where('id',$value['media_id'])->value('media');
            }
            $customer_amount = $CrmCustomerM->customer_select(array_merge($query, ['source_type'=>$value['source_type']],['source'=>['not like','%未付款%']]));
            $value['amount']=$amount = count($customer_amount);
            $customer_id_arr = array_column($customer_amount,'id');
            $refund = $CrmCustomerM->customer_count(array_merge(['id'=>['in',$customer_id_arr]],['is_refund'=>1]));
            $conversion_select = $CrmCustomerM->customer_select(array_merge(['id'=>['in',$customer_id_arr]],['public_paytype'=>['in',[2,3]]]));
            $value['conversion'] = count($conversion_select);
            $conversion = $value['conversion'];
            $value['yaoyue_wuxiao'] = $yaoyue_wuxiao =  $CrmCustomerM->customer_count(array_merge(['id'=>['in',$customer_id_arr]],['yaoyue_status'=>['in',[2,5]]]))+$refund;
//            $customer_become_zhifu = array_column($CrmCustomerM->customer_select(array_merge($query,['source_type'=>$value['source_type']],['source'=>['like','%未付款%']])),'id');
            $value['youxiao']=$amount - $value['yaoyue_wuxiao']+ $CrmCustomerM->customer_count(array_merge($query,['source_type'=>$value['source_type']],['source'=>['like','%未付款%']],['become_zhifu'=>1]));

            $value['refund']=$refund;
            $value['refund_rate']='0%';
            $value['wuxiao_rate']='0%';
            $value['youxiao_rate']='0%';
            $value['conversion']=$conversion;
            if($amount>0){
                $value['refund_rate']=(round(($value['refund']/$amount),4)*100).'%';
                $value['wuxiao_rate']=(round(($yaoyue_wuxiao/$amount),4)*100).'%';
                $value['youxiao_rate']=(round(($value['youxiao']/$amount),4)*100).'%';
            }
            $data['total_amount']+=$amount;
            $data['total_refund_amount']+=$value['refund'];
            $data['total_conversion'] +=$value['conversion'];
            $data['wuxiao_amount']+= $yaoyue_wuxiao;
            $data['youxiao_amount'] += $value['youxiao'];
        }
        if($data['total_amount']>0){
            $data['total_refund_rate'] = (round(($data['total_refund_amount']/$data['total_amount']),4)*100).'%';
            $data['wuxiao_rate'] = (round(($data['wuxiao_amount']/$data['total_amount']),4)*100).'%';
            $data['youxiao_rate'] = (round(($data['youxiao_amount']/$data['total_amount']),4)*100).'%';
        }
        $data['list']=$source_type_group;
        $data['total']=$totals;
        $data['starttime']=$query['starttime'];
        $data['endtime']=$query['endtime'];
        apiReturn(200,'进线报表',$data);
    }

    /*
     *渠道数据报表
     *  */
    public function sourceCount()
    {
        $data = input();//token starttime endtime type链接type

        $where = [];
        $where['source_type'] = array('>',0);
        if (isset($data['type'])) {
            if (!empty($data['type'])) {
                $where['source_type'] = $data['type'];
            }
        }
        $time = '';
        if (isset($data['starttime']) && isset($data['endtime'])) {
            if (!empty($data['starttime']) && !empty($data['endtime'])) {
                $where['date'] = array(array('egt',strtotime($data['starttime'])),array('elt',strtotime($data['endtime'])));
                if (substr($data['starttime'], 0,10) == substr($data['endtime'], 0,10)) {
                    $time = substr($data['starttime'], 0,10);
                }else{
                    $time = substr($data['starttime'], 0,10).'-'.substr($data['endtime'], 0,10);
                }
            }else{
                apiReturn(201,'时间不能为空');
            }
        }else{
            apiReturn(201,'时间不能为空');
        }
        
        //渠道进线列表
        $typeList = db('crm_customer')->distinct(true)->field('source_type')->where($where)->select();

        foreach ($typeList as $key => $value) {
            $jinxian = 0;//进线数
            $tuikuan = 0;//退款数
            $tuikuan_rate = 0;//退款率
            $baoke = 0;//报课数
            $zhuanhua = 0;//转化率
            $danfenjia = 0;//单粉价

            $where1['date'] = $where['date'];
            $where1['source_type'] = $value['source_type'];
            $jinxian = db('crm_customer')->where($where1)->count();

            $where2['date'] = $where['date'];
            $where2['source_type'] = $value['source_type'];
            $where2['is_refund'] = 1;
            $tuikuan = db('crm_customer')->where($where2)->count();

            if (!empty($jinxian)) {
                $tuikuan_rate = sprintf("%.2f",$tuikuan/$jinxian);
            }else{
                $tuikuan_rate = 0;
            }
            $baoke = db('crm_customer')->where(array('source_type'=>$value['source_type'],'public_paytype'=>array('in','2,3')))->count();
            if (!empty($jinxian)) {
                $zhuanhua = sprintf("%.2f",$baoke/$jinxian);
            }else{
                $zhuanhua = 0;
            }
            $total = db('crm_customer')->where(array('source_type'=>$value['source_type']))->sum('total');
            if (!empty($jinxian)) {
                $danfenjia = sprintf("%.2f",$total/$jinxian);
            }else{
                $danfenjia = 0;
            }
            $typeList[$key]['time'] = $time;
            $typeList[$key]['type'] = $value['source_type'];
            $typeList[$key]['jinxian'] = $jinxian;
            $typeList[$key]['tuikuan'] = $tuikuan;
            $typeList[$key]['tuikuan_rate'] = $tuikuan_rate;
            $typeList[$key]['baoke'] = $baoke;
            $typeList[$key]['zhuanhua'] = $zhuanhua;
            $typeList[$key]['danfenjia'] = $danfenjia;
        }

        apiReturn(200,'获取渠道进线统计成功',$typeList);
    }





















    /*
     *邀约数据报表
     *  */ 
    public function yaoyueCount()
    {
        $data = input();//token starttime endtime zu_id course_id course_status首课进度

        if (isset($data['starttime']) && isset($data['endtime'])) {
            if (!empty($data['starttime']) && !empty($data['endtime'])) {
                $where['yaoyue_zhuanyi_date'] = array(array('egt',strtotime($data['starttime'])),array('elt',strtotime($data['endtime'])));
            }else{
                apiReturn(201,'时间不能为空');
            }
        }else{
            apiReturn(201,'时间不能为空');
        }
        if (isset($data['zu_id'])) {
            if (!empty($data['zu_id'])) {
                $wherezu_id = $this->getFenzuList($data['zu_id']);
                $where1['username'] = $this->getManageList($wherezu_id);
            }
        }
        if (isset($data['course_id'])) {
            if (!empty($data['course_id'])) {
                $where['public_yingqi'] = $data['course_id'];
            }
        }

        $manage_author = $this->getManageAuthor();
        if ($manage_author['is_xiaoshou'] == 1) {
            $where1['username'] = $this->getManageList();
        }
        $where1['type'] = 1;

        $yaoyueManage = db('crm_manage')->field('username,name,zu_id,fenzu_name')->where($where1)->order('zu_id ASC,id ASC')->select();

        foreach ($yaoyueManage as $key => $value) {
            $yaoyue_number = 0;//进线数
            $yaoyue_zhifu = 0;//已支付数量
            $yaoyue_weizhifu = 0;//未支付数量
            $yaoyue_becomezhifu = 0;//未支付变为支付
            $shiji = 0;//实际有效数据
            $yaoyue_jiaweixin = 0;//已加微
            $jiawei_rate = 0;//加微率
            $yaoyue_yijieting = 0;//已接听未加微
            $yaoyue_weijieting = 0;//未接听
            $yaoyue_konghao = 0;//空号不是本人
            $yaoyue_tuifei = 0;//申请退费
            $tuifei_rate = 0;//退费率
            $zhanghao = 0;//测试账户
            $zhanghao_rate = 0;//测试账户率
            $jinqun = 0;//进群数量
            $jinqun_rate = 0;//进群率
            $paike = 0;//排课数
            $paike_rate = 0;//排课率
            $buzhu = 0;//不足10分钟
            $dayu = 0;//大于10分钟

            $where['yaoyue_manage_id'] = $value['username'];
            $customerList = db('crm_customer')->where($where)->select();
            foreach ($customerList as $key1 => $value1) {
                $yaoyue_number++;
                if ($value1['source'] == '39元公开课未付款') {
                    $yaoyue_weizhifu++;
                    if ($value1['become_zhifu'] == 1) {
                        $yaoyue_becomezhifu++;
                    }
                }else{
                    $yaoyue_zhifu++;
                }
                if ($value1['yaoyue_status'] == 1) {
                    $yaoyue_jiaweixin++;
                }elseif ($value1['yaoyue_status'] == 2) {
                    $yaoyue_yijieting++;
                }elseif ($value1['yaoyue_status'] == 3) {
                    $yaoyue_weijieting++;
                }elseif ($value1['yaoyue_status'] == 4) {
                    $yaoyue_tuifei++;
                }elseif ($value1['yaoyue_status'] == 4) {
                    $yaoyue_konghao++;
                }else{

                }
                if ($value1['keqian_status1'] == 1) {
                    $jinqun++;
                }
                if ($value1['keqian_status2'] == 1) {
                    $zhanghao++;
                }
                if (!empty($value1['public_yingqi'])) {
                    $paike++;
                    $shouke_status = db('crm_durations')->where(array('course_id'=>$value1['public_yingqi_course1'],'phone'=>$value1['phone']))->value('status');
                    if (empty($shouke_status) || $shouke_status == 1) {
                        $buzhu++;
                    }else{
                        $dayu++;
                    }
                }
            }
            $yaoyueManage[$key]['yaoyue_number'] = $yaoyue_number;
            $yaoyueManage[$key]['yaoyue_zhifu'] = $yaoyue_zhifu;
            $yaoyueManage[$key]['yaoyue_weizhifu'] = $yaoyue_weizhifu;
            $yaoyueManage[$key]['yaoyue_becomezhifu'] = $yaoyue_becomezhifu;
            $shiji = $yaoyue_zhifu + $yaoyue_becomezhifu;
            $yaoyueManage[$key]['shiji'] = $shiji;
            $yaoyueManage[$key]['yaoyue_jiaweixin'] = $yaoyue_jiaweixin;
            if (!empty($shiji)) {
                $yaoyueManage[$key]['jiawei_rate'] = intval(sprintf("%.2f",$yaoyue_jiaweixin/$shiji)*100);
            }else{
                $yaoyueManage[$key]['jiawei_rate'] = 0;
            }
            $yaoyueManage[$key]['yaoyue_yijieting'] = $yaoyue_yijieting;
            $yaoyueManage[$key]['yaoyue_weijieting'] = $yaoyue_weijieting;
            $yaoyueManage[$key]['yaoyue_tuifei'] = $yaoyue_tuifei;
            if (!empty($shiji)) {
                $yaoyueManage[$key]['tuifei_rate'] = intval(sprintf("%.2f",$yaoyue_tuifei/$shiji)*100);
            }else{
                $yaoyueManage[$key]['tuifei_rate'] = 0;
            }
            $yaoyueManage[$key]['yaoyue_konghao'] = $yaoyue_konghao;
            $yaoyueManage[$key]['jinqun'] = $jinqun;
            if (!empty($shiji)) {
                $yaoyueManage[$key]['jinqun_rate'] = intval(sprintf("%.2f",$jinqun/$shiji)*100);
            }else{
                $yaoyueManage[$key]['jinqun_rate'] = 0;
            }
            $yaoyueManage[$key]['zhanghao'] = $zhanghao;
            if (!empty($shiji)) {
                $yaoyueManage[$key]['zhanghao_rate'] = intval(sprintf("%.2f",$zhanghao/$shiji)*100);
            }else{
                $yaoyueManage[$key]['zhanghao_rate'] = 0;
            }
            $yaoyueManage[$key]['paike'] = $paike;
            if (!empty($shiji)) {
                $yaoyueManage[$key]['paike_rate'] = intval(sprintf("%.2f",$paike/$shiji)*100);
            }else{
                $yaoyueManage[$key]['paike_rate'] = 0;
            }
            $yaoyueManage[$key]['buzhu'] = $buzhu;
            $yaoyueManage[$key]['dayu'] = $dayu;
        }
        // var_dump($yaoyueManage);exit;
        apiReturn(200,'邀约数据报表',$yaoyueManage);
    }


    public function yaoyueCount2(){
        $query = input();
        $query['limit'] = isset($query['limit'])?$query['limit']:10;
        $query['page'] = isset($query['page'])?$query['page']:1;
        if(isset($query['endtime']) && isset($query['starttime'])){
            if($query['starttime']>$query['endtime'])apiReturn(400,'查询开始日期不能大于结束日期');
        }
        if(empty($query['starttime'])|| empty($query['endtime'])){
            $query['starttime'] = date('Y-m-d H:i:s',mktime(0, 0, 0, date('m'), date('d'), date('Y'))) ;
            $query['endtime'] =date('Y-m-d H:i:s',mktime(23, 59, 59, date('m'), date('d'), date('Y'))) ;
        }
        $CustomerManageM = new CrmCustomerManage();
        $crmDurationsM = new CrmDurations();
        $total =$this->countWhere($CustomerManageM,$query)[0]->count();
        $customerManage=$this->countWhere($CustomerManageM,$query);
        $res = $customerManage[0]->field('c.manage_id as username,f.name as fenzu_name,m.name,count(*) as yaoyue_number')->limit($query['limit'])->page($query['page'])->order('yaoyue_number desc')->select();
        $wheres['date']=['between',[strtotime($query['starttime']),strtotime($query['endtime'])]];
        $weixin_total=0;
        $yaoyue_total=0;
        $refund_total=0;
        $youxiao_total=0;
        $jinqun_total=0;
        $paike_total=0;
        $ceshi_total=0;
        $paid_total=0;
        foreach ($res as &$value){
            $arr['manage_id']=$value['username'];
            if(isset($customerManage[1])){
               $arr= array_merge($customerManage[1],$arr);
            }
            $paid=$CustomerManageM->where($wheres)->where($arr)->where(['source'=>['not like','%未付款%']])->select();
            $value['paid']=count($paid);
            $paid_total+=$value['paid'];
            $value['unpaid']=$value['yaoyue_number']- $value['paid'];
            $CustomerManage_client_id = array_column($paid,'client_id');
            $yaoyue=$CustomerManageM->where('client_id','in',$CustomerManage_client_id)->where($arr)->select();
            $value['weixin']=0;
            $value['weixin_rate'] = '0%';
            $value['answered']=0;
            $value['not_answer']=0;
            $value['refund']=0;
            $value['refund_rate'] = '0%';
            $value['ceshi']=0;
            $value['group_entry']=0;
            if(count($yaoyue)>0){
                foreach ($yaoyue as $val){
                    if($val['yaoyue_status']==1){
                        $value['weixin']+=1;
                    }elseif ($val['yaoyue_status']==2){
                        $value['answered']+=1;
                    }elseif ($val['yaoyue_status']==3){
                        $value['not_answer']+=1;
                    }elseif ($val['yaoyue_status']==4){
                        $value['refund']+=1;
                    }
                    if($value['yaoyue_number']>0){
                        if($val['keqian_status2']==1){
                            $value['ceshi']+=1;
                        }
                        if($val['keqian_status1']==1){
                            $value['group_entry']+=1;
                        }
                    }
                }
            }
            $ceshi_total+=$value['ceshi'];
            $jinqun_total+=$value['group_entry'];
            $customer_become_zhifu = array_column($CustomerManageM->where($wheres)->where($arr)->select(),'client_id');
            $value['youxiao']=$value['paid']-$value['refund']+$CustomerManageM->where($arr)->where('client_id','in',$customer_become_zhifu)->where('become_zhifu',1)->count();
            $value['wuxiao']=$CustomerManageM->where($arr)->where(['yaoyue_status'=>['in',[2,5]],'client_id'=>['in',$CustomerManage_client_id]])->count()+ $value['refund'];//为空+退款+已接听未加微
            $weixin_total+= $value['weixin'];
            $yaoyue_total+=$value['yaoyue_number'];
            $refund_total+=$value['refund'];
            $youxiao_total+=$value['youxiao'];
            $value['group_entry']=0;
            $value['paike']=0;
            $value['public_yingqi_num']=0;
            $value['ceshi_rate'] = '0%';
            $value['group_entry_rate'] = '0%';
            $value['paike_rate']='0%';
            $value['public_yingqi_rate']='0%';
            $value['da_shi']=0;
            $value['xiao_shi']=0;
            if($value['paid']>0){
                $value['weixin_rate']=(round(($value['weixin']/$value['paid']),4)*100).'%';
                $value['group_entry_rate'] = (round(($value['group_entry']/ $value['paid']),4)*100).'%';
            }
            if($value['youxiao']>0){
                $value['ceshi_rate']=(round(($value['ceshi']/$value['youxiao']),4)*100).'%';
            }
            if(isset($customerManage[1])){
                $yycourse = CrmCustomerYycourse::where($wheres)->where(['manage_id'=>$arr['manage_id'],'course_id'=>['like',"%".$customerManage[1]['public_yingqi']."%"]])->count();
                $value['public_yingqi_num']=$CustomerManageM->where($wheres)->where($arr)->count()+$yycourse;
            }else{
                $yycourse = CrmCustomerYycourse::where($wheres)->where(['manage_id'=>$arr['manage_id']])->count();
                $value['public_yingqi_num']=$CustomerManageM->where($wheres)->where($arr)->where(['public_yingqi'=>'is not null','public_yingqi'=>['<>',0]])->count()+$yycourse;
            }

            if($value['youxiao']>0){
                $client_phone = $CustomerManageM->where('client_id','in',$CustomerManage_client_id)->where($arr)->order('id desc')->group('client_id')->column('phone');
                $value['da_shi']=$crmDurationsM->where('status','>',1)->where('phone','in',$client_phone)->group('phone')->count();
                $value['xiao_shi']=$crmDurationsM->where('status','<=',1)->where('phone','in',$client_phone)->group('phone')->count();
                $value['refund_rate']=(round(($value['refund']/ $value['paid']),4)*100).'%';
                $value['public_yingqi_rate']=(round(($value['public_yingqi_num']/ $value['youxiao']),4)*100).'%';
            }
            $paike_total+=$value['public_yingqi_num'];
            $value['public_yingqi_num']=$value['public_yingqi_num']-$yycourse.'/'. $yycourse;
        }
        $stat['weixin_total_rate']='0%';
        $stat['refund_total_rate']='0%';
        $stat['jinqun_total_rate']='0%';
        $stat['paike_total_rate']='0%';
        $stat['ceshi_total_rate']='0%';
        if($youxiao_total>0){
            $stat['paike_total_rate']=(round(($paike_total/$youxiao_total),4)*100).'%';
            $stat['ceshi_total_rate']=(round(($ceshi_total/$youxiao_total),4)*100).'%';
        }
        if($paid_total>0){
            $stat['weixin_total_rate']=(round(($weixin_total/$paid_total),4)*100).'%';
            $stat['refund_total_rate']=(round(($refund_total/$paid_total),4)*100).'%';
            $stat['jinqun_total_rate']=(round(($jinqun_total/$paid_total),4)*100).'%';
        }
        $stat['paike_total']=$paike_total;
        apiReturn(200,'成功',['list'=>$res,'total'=>$total,'stat'=>$stat]);
    }

    public function countWhere($CustomerManageM,$query,$type=1){
        $where['c.date']=['between',[strtotime($query['starttime']),strtotime($query['endtime'])]];
        $CustomerManageM->alias('c')->group('c.manage_id')->join('crm_manage m','c.manage_id=m.username')->join('crm_fenzu f','f.id=m.zu_id')->where($where)->where('m.type',$type);
        if(isset($query['zu_id']) && $query['zu_id']){
            $zu_arr = (new CrmFenzu())->fenzu($query['zu_id']);
            array_push($zu_arr,(int)$query['zu_id']);
            $CustomerManageM->where('m.zu_id','in',$zu_arr);
        }
        if(isset($query['course_id']) && $query['course_id']){
            $CustomerManageM->where('c.public_yingqi',$query['course_id']);
            $public_yingqi_where['public_yingqi']=$query['course_id'];
        }
        if (isset($public_yingqi_where)) return [$CustomerManageM,$public_yingqi_where];
        return [$CustomerManageM];
    }

    /*
     *教务数据报表
     *  */ 
    public function zhuidanCount()
    {
        $data = input();//token starttime endtime zu_id course_id

        if (isset($data['starttime']) && isset($data['endtime'])) {
            if (!empty($data['starttime']) && !empty($data['endtime'])) {
                $where['jiaowu_zhuanyi_date'] = array(array('egt',strtotime($data['starttime'])),array('elt',strtotime($data['endtime'])));
            }else{
                apiReturn(201,'时间不能为空');
            }
        }else{
            apiReturn(201,'时间不能为空');
        }
        if (isset($data['course_id'])) {
            if (!empty($data['course_id'])) {
                $where['public_yingqi'] = $data['course_id'];
            }
        }
        if (isset($data['zu_id'])) {
            if (!empty($data['zu_id'])) {
                $wherezu_id = $this->getFenzuList($data['zu_id']);
                $where1['username'] = $this->getManageList($wherezu_id);
            }
        }

        $manage_author = $this->getManageAuthor();
        if ($manage_author['is_xiaoshou'] == 1) {
            $where1['username'] = $this->getManageList();
        }
        $where1['type'] = 2;

        $zhuidanManage = db('crm_manage')->field('username,name,zu_id,fenzu_name')->where($where1)->order('zu_id ASC,id ASC')->select();

        foreach ($zhuidanManage as $key => $value) {
            $customer_number = 0;//数据总量
            $customer_zhifu = 0;//已支付数量
            $customer_weizhifu = 0;//未支付数量
            $customer_becomezhifu = 0;//未支付变为支付
            $shiji = 0;//实际有效数据
            $jiaweixin = 0;//微信添加人数
            $jiaweixin_rate = 0;//微信添加率
            $xiadan = 0;//直播间成交
            $zhuidan = 0;//追单成交
            $zhuidan_rate = 0;//追单比例
            $chengjiao = 0;//总成交数
            $yeji = 0;//总业绩
            $kaifa_rate = 0;//转化率
            $shiji_rate = 0;//实际转化率（管理员）
            $danfenjia = 0;//单粉价（管理员）

            $where['jiaowu_manage_id'] = $value['username'];
            $customerList = db('crm_customer')->where($where)->select();
            foreach ($customerList as $key1 => $value1) {
                $customer_number++;
                if ($value1['source'] == '39元公开课未付款') {
                    $customer_weizhifu++;
                    if ($value1['become_zhifu'] == 1) {
                        $customer_becomezhifu++;
                    }
                }else{
                    $customer_zhifu++;
                }
                if ($value1['yaoyue_status'] == 1) {
                    $jiaweixin++;
                }
                if ($value1['public_paytype'] == 2) {
                    $xiadan++;
                }
                if ($value1['public_paytype'] == 3) {
                    $zhuidan++;
                }
                $yeji += $value1['total'];
            }

            $zhuidanManage[$key]['customer_number'] = $customer_number;
            $zhuidanManage[$key]['customer_zhifu'] = $customer_zhifu;
            $zhuidanManage[$key]['customer_weizhifu'] = $customer_weizhifu;
            $zhuidanManage[$key]['customer_becomezhifu'] = $customer_becomezhifu;
            $shiji = $customer_zhifu + $customer_zhifu;
            $zhuidanManage[$key]['shiji'] = $shiji;
            $zhuidanManage[$key]['jiaweixin'] = $jiaweixin;
            if (!empty($shiji)) {
                $zhuidanManage[$key]['jiaweixin_rate'] = intval(sprintf("%.2f",$jiaweixin/$shiji)*100);
            }else{
                $zhuidanManage[$key]['jiaweixin_rate'] = 0;
            }
            $zhuidanManage[$key]['xiadan'] = $xiadan;
            $zhuidanManage[$key]['zhuidan'] = $zhuidan;
            if (!empty($shiji)) {
                $zhuidanManage[$key]['zhuidan_rate'] = intval(sprintf("%.2f",$zhuidan/$shiji)*100);
            }else{
                $zhuidanManage[$key]['zhuidan_rate'] = 0;
            }
            $chengjiao = $xiadan + $zhuidan;
            $zhuidanManage[$key]['chengjiao'] = $chengjiao;
            $zhuidanManage[$key]['yeji'] = $yeji;
            if (!empty($customer_number)) {
                $zhuidanManage[$key]['kaifa_rate'] = intval(sprintf("%.2f",$chengjiao/$customer_number)*100);
            }else{
                $zhuidanManage[$key]['kaifa_rate'] = 0;
            }
            if (!empty($shiji)) {
                $zhuidanManage[$key]['shiji_rate'] = intval(sprintf("%.2f",$chengjiao/$shiji)*100);
            }else{
                $zhuidanManage[$key]['shiji_rate'] = 0;
            }
            if (!empty($shiji)) {
                $zhuidanManage[$key]['danfenjia'] = intval(sprintf("%.2f",$yeji/$shiji)*100);
            }else{
                $zhuidanManage[$key]['danfenjia'] = 0;
            }
        }
        
        apiReturn(200,'追单数据报表',$zhuidanManage);
    }


    /*
     *教务数据报表
     *  */
    public function zhuidanCount2(){
        $query = input();
        $query['limit'] = isset($query['limit'])?$query['limit']:10;
        $query['page'] = isset($query['page'])?$query['page']:1;
        if(isset($query['endtime']) && isset($query['starttime'])){
            if($query['starttime']>$query['endtime'])apiReturn(400,'查询开始日期不能大于结束日期');
        }
        if(empty($query['starttime'])|| empty($query['endtime'])){
            $query['starttime'] = date('Y-m-d H:i:s',mktime(0, 0, 0, date('m'), date('d'), date('Y'))) ;
            $query['endtime'] =date('Y-m-d H:i:s',mktime(23, 59, 59, date('m'), date('d'), date('Y'))) ;
        }
        $CustomerManageM = new CrmCustomerManage();
        $total =$this->countWhere($CustomerManageM,$query,2)[0]->count();
        $customerManage=$this->countWhere($CustomerManageM,$query,2);
        $res = $customerManage[0]->field('c.manage_id as username,f.name as fenzu_name,m.name,count(*) as yaoyue_number')->limit($query['limit'])->page($query['page'])->order('yaoyue_number desc')->select();
        $wheres['date']=['between',[strtotime($query['starttime']),strtotime($query['endtime'])]];
        $weixin_total=0;
        $yaoyue_total=0;
        $chengjiao_total=0;
        $zhuidan_pay_total=0;
        $paid_total = 0;
        $youxiao_total = 0;
        $yeji_total=0;
        foreach ($res as &$value){
            $arr['manage_id']=$value['username'];
            if(isset($customerManage[1])){
                $arr= array_merge($customerManage[1],$arr);
            }
            $paid=$CustomerManageM->where($wheres)->where($arr)->where(['source'=>['not like','%未付款%']])->select();
            $value['paid']=count($paid);
            $paid_total+=$value['paid'];
            $value['unpaid']=$value['yaoyue_number']- $value['paid'];
            $CustomerManage_client_id = array_column($paid,'client_id');
            $value['wuxiao'] =$CustomerManageM->where($arr)->where(['yaoyue_status'=>['in',[2,5]],'is_refund'=>1,'client_id'=>['in',$CustomerManage_client_id]])->count();
            $customer_become_zhifu = array_column($CustomerManageM->where($wheres)->where($arr)->select(),'client_id');
            $become_zhifu = $CustomerManageM->where($arr)->where('client_id','in',$customer_become_zhifu)->where('become_zhifu',1)->select();
            $value['become_zhifu'] =count($become_zhifu);
            $value['youxiao']=$value['paid']-$value['wuxiao']+count($become_zhifu);
            $youxiao_total+= $value['youxiao'];
            $paytype = $CustomerManageM->where('client_id','in',array_merge($CustomerManage_client_id,array_column($become_zhifu,'client_id')))->where($arr)->where('public_paytype','in',[2,3])->select();
            $value['zhibo_pay']=0;
            $value['zhuidan_pay']=0;
            $value['zhuidan_pay_rate']='0%';
            $value['total_chengjiao']=count($paytype);
            $chengjiao_total+= $value['total_chengjiao'];
            $value['total_yeji']=0;
            if(count($paytype)>0){
                foreach ($paytype as $val){
                    $value['total_yeji']+=2980;
                    if($val['public_paytype']==2){
                        $value['zhibo_pay']+=1;
                    }elseif ($val['public_paytype']==3){
                        $value['zhuidan_pay']+=1;
                    }
                }
                $zhuidan_pay_total+=$value['zhuidan_pay'];
                $value['zhuidan_pay_rate']=(round((  $value['zhuidan_pay']/ $value['total_chengjiao']),4)*100).'%';
            }
            $value['shiji_rate'] = '0%';
            $value['price'] = 0;
            $yeji_total+=$value['total_yeji'];
            if($value['youxiao']>0){
                $value['shiji_rate']=(round(( $value['total_chengjiao']/  $value['youxiao']),4)*100).'%';//成交数除以有效订单数
                $value['price'] = round(( $value['total_yeji']/ $value['youxiao']),2);
            }
            $value['weixin']=$CustomerManageM->where($wheres)->where($arr)->where('yaoyue_status',1)->count();;
            $weixin_total+= $value['weixin'];
            $yaoyue_total+=$value['yaoyue_number'];
            $value['weixin_rate'] = '0%';
            $value['zhuanhua_rate']='0%';
            if($value['paid']>0){
                $value['weixin_rate']=(round(($value['weixin']/$value['paid']),4)*100).'%';
                $value['zhuanhua_rate'] = (round(( $value['total_chengjiao']/ $value['paid']),4)*100).'%';//成交数除以订单数
            }
        }
        $stat['weixin_total_rate']='0%';
        $stat['zhuidan_order_total_rate']='0%';
        if($chengjiao_total>0){
            $stat['zhuidan_order_total_rate']=(round(($zhuidan_pay_total/$chengjiao_total),4)*100).'%';
        }
        $stat['zhuidan_zhuanhua_total_rate']='0%';
        if($paid_total>0){
            $stat['weixin_total_rate']=(round(($weixin_total/$paid_total),4)*100).'%';
            $stat['zhuidan_zhuanhua_total_rate']=(round(($chengjiao_total/$paid_total),4)*100).'%';
        }
        $stat['zhuidan_zhuanhua2_total_rate']='0%';
        if($youxiao_total>0){
            $stat['zhuidan_zhuanhua2_total_rate']=(round(($chengjiao_total/$youxiao_total),4)*100).'%';
            $stat['yeji_total_price']=round(($yeji_total/$youxiao_total),2);
        }
        apiReturn(200,'成功',['list'=>$res,'total'=>$total,'stat'=>$stat]);
    }

    /*
     *公开课数据报表
     *  */ 
    public function publicCourseCount()
    {
        $data = input();//token starttime endtime course_id

        $where = [];
        $where['search_type'] = array('in','1,2,3');
        $where['id'] = array('notin','67,78');
        if (isset($data['starttime']) && isset($data['endtime'])) {
            if (!empty($data['starttime']) && !empty($data['endtime'])) {
                // $where['time'] = array(array('egt',strtotime($data['starttime'])),array('elt',strtotime($data['endtime'])));
                $where['time'] = 0;
            }else{
                apiReturn(201,'时间不能为空');
            }
        }else{
            apiReturn(201,'时间不能为空');
        }
        if (isset($data['course_id'])) {
            if (!empty($data['course_id'])) {
                $where['id'] = $data['course_id'];
            }
        }

        $publicCourse = db('goods')->field('id,title,is_next')->where($where)->order('id DESC')->select();

        foreach ($publicCourse as $key => $value) {
            $paike = 0;//排课人数
            $weishangke = 0;//未上课人数
            $shouke = 0;//首课到课
            $shouke_daoke_rate = 0;//到课率
            $shouke_status1 = 0;//不足10分钟人数
            $shouke_status2 = 0;//10-30分钟人数
            $shouke_status3 = 0;//30-60分钟人数
            $shouke_status4 = 0;//1小时-2小时人数
            $shouke_status5 = 0;//大于2小时人数
            $shouke_status6 = 0;//作业数
            $erke = 0;//首课到课
            $erke_daoke_rate = 0;//到课率
            $erke_status1 = 0;//不足10分钟人数
            $erke_status2 = 0;//10-30分钟人数
            $erke_status3 = 0;//30-60分钟人数
            $erke_status4 = 0;//1小时-2小时人数
            $erke_status5 = 0;//大于2小时人数
            $erke_status6 = 0;//作业数

            $paike = db('goods_phone')->where(array('course_id'=>$value['id']))->count();
            $weishangke = db('goods_phone')->where(array('course_id'=>$value['id'],'status'=>0))->count();

            $next_course = db('goods_next')->field('id,title')->where(array('goods_id'=>$value['id']))->order('id ASC')->limit(0,2)->select();
            if (count($next_course) != 2) {
                unset($publicCourse[$key]);
                continue;
            }
            if (!empty($next_course)) {
                $shouke = db('crm_durations')->where(array('course_id'=>$next_course[0]['id']))->count();
                $shouke_status1 = db('crm_durations')->where(array('course_id'=>$next_course[0]['id'],'status'=>1))->count();
                $shouke_status2 = db('crm_durations')->where(array('course_id'=>$next_course[0]['id'],'status'=>2))->count();
                $shouke_status3 = db('crm_durations')->where(array('course_id'=>$next_course[0]['id'],'status'=>3))->count();
                $shouke_status4 = db('crm_durations')->where(array('course_id'=>$next_course[0]['id'],'status'=>4))->count();
                $shouke_status5 = db('crm_durations')->where(array('course_id'=>$next_course[0]['id'],'status'=>5))->count();
                $shouke_status6 = db('crm_durations')->where(array('course_id'=>$next_course[0]['id'],'status'=>6))->count();

                $erke = db('crm_durations')->where(array('course_id'=>$next_course[1]['id']))->count();
                $erke_status1 = db('crm_durations')->where(array('course_id'=>$next_course[1]['id'],'status'=>1))->count();
                $erke_status2 = db('crm_durations')->where(array('course_id'=>$next_course[1]['id'],'status'=>2))->count();
                $erke_status3 = db('crm_durations')->where(array('course_id'=>$next_course[1]['id'],'status'=>3))->count();
                $erke_status4 = db('crm_durations')->where(array('course_id'=>$next_course[1]['id'],'status'=>4))->count();
                $erke_status5 = db('crm_durations')->where(array('course_id'=>$next_course[1]['id'],'status'=>5))->count();
                $erke_status6 = db('crm_durations')->where(array('course_id'=>$next_course[1]['id'],'status'=>6))->count();
            }
            if (!empty($paike)) {
                $shouke_daoke_rate = sprintf("%.2f",$shouke/$paike);
            }else{
                $shouke_daoke_rate = 0;
            }
            if (!empty($paike)) {
                $erke_daoke_rate = sprintf("%.2f",$erke/$paike);
            }else{
                $erke_daoke_rate = 0;
            }
            $publicCourse[$key]['paike'] = $paike;
            $publicCourse[$key]['weishangke'] = $weishangke;
            $publicCourse[$key]['shouke'] = $shouke;
            $publicCourse[$key]['shouke_daoke_rate'] = $shouke_daoke_rate;
            $publicCourse[$key]['shouke_status1'] = $shouke_status1;
            $publicCourse[$key]['shouke_status2'] = $shouke_status2;
            $publicCourse[$key]['shouke_status3'] = $shouke_status3;
            $publicCourse[$key]['shouke_status4'] = $shouke_status4;
            $publicCourse[$key]['shouke_status5'] = $shouke_status5;
            $publicCourse[$key]['shouke_status6'] = $shouke_status6;
            $publicCourse[$key]['shouke_status6'] = $paike;
            $publicCourse[$key]['erke'] = $erke;
            $publicCourse[$key]['erke_daoke_rate'] = $erke_daoke_rate;
            $publicCourse[$key]['erke_status1'] = $erke_status1;
            $publicCourse[$key]['erke_status2'] = $erke_status2;
            $publicCourse[$key]['erke_status3'] = $erke_status3;
            $publicCourse[$key]['erke_status4'] = $erke_status4;
            $publicCourse[$key]['erke_status5'] = $erke_status5;
            $publicCourse[$key]['erke_status6'] = $erke_status6;
        }

        apiReturn(200,'公开课数据报表',$publicCourse);
    }


    public function yingqi($query){
        $crmCustomerM = new CrmCustomer();
        if(isset($query['course_id']) && $query['course_id']){
            $crmCustomerM->where('public_yingqi',$query['course_id']);
        }
        return $crmCustomerM;
    }

    public function publicCourseCount2(){
        $query = input();
        $query['limit'] = isset($query['limit'])?$query['limit']:10;
        $query['page'] = isset($query['page'])?$query['page']:1;
        if(isset($query['endtime']) && isset($query['starttime'])){
            if($query['starttime']>$query['endtime'])apiReturn(400,'查询开始日期不能大于结束日期');
        }
        foreach ($query as $k=>$v){
            if (empty($v))unset($query[$k]);
        }
        $goodsNextM = new GoodsNext();
        $courseDurationM = new CrmCourseDuration();
        $goodsM = new Goods();
        $crmCustomerM = new CrmCustomer();
        $res = $goodsM->where(function ($querys) use ($query){
            if(isset($query['course_id'])){
                $querys->where('id',$query['course_id']);
            }
            if(isset($query['course_type'])){
                if($query['course_type']==1){
                    $querys->where('search_type','in',[1,2,3]);
                }else{
                    $querys->where('search_type','not in',[1,2,3]);
                }
            }
        })->field('id,title')->limit($query['limit'])->page($query['page'])->order('id desc')->select();
        $total =  $goodsM->where(function ($querys) use ($query){
            if(isset($query['course_id'])){
                $querys->where('id',$query['course_id']);
            }
            if(isset($query['course_type'])){
                if($query['course_type']==1){
                    $querys->where('search_type','in',[4,5,6]);
                }else{
                    $querys->where('search_type','not in',[4,5,6]);
                }
            }
        })->field('id,title')->count();
        foreach ($res as &$value){
            if(isset($value['id'])){
                $paike = $crmCustomerM->where('public_yingqi',$value['id'])->select();
                $value['paike']=count($paike);
                $phone_arr = array_column($paike,'phone');
                $goodsNextId = $goodsNextM->where('goods_id',$value['id'])->order('id desc')->column('id');
                $durations = $courseDurationM->where('course_id','in',$goodsNextId)->where('phone','in',$phone_arr)->where('playStatus','live')->group('phone,course_id')->field('*,SUM(playDuration) as play')->select();
                $d =  $courseDurationM->where('course_id','in',$goodsNextId)->where('phone','in',$phone_arr)->where('playStatus','live')->group('phone')->count();//count($durations);
                $value = $this->paike($value,$durations,$goodsNextId,$d);
            }
        }
        apiReturn(200,'公开课数据报表',['list'=>$res,'total'=>$total]);
    }

    public function durations($playDuration){
        if($playDuration<=60*10){
            $status =  1;
        }elseif ($playDuration>60*10 && $playDuration<=60*30){
            $status = 2;
        }elseif ($playDuration>60*30 && $playDuration<=60*60){
            $status = 3;
        }elseif ($playDuration>60*60 && $playDuration<=60*120){
            $status = 4;
        }elseif ($playDuration>60*120){
            $status =5;
        }else{
            $status =0;
        }
        return $status;
    }

    public function publicCourseCountDetails(){
        $query = input();
        if(empty($query['course_id']))apiReturn(4000,'缺少参数');
        $CrmFenzuM =  new CrmFenzu();
        $crmCustomerM = new CrmCustomer();
        $courseDurationM = new CrmCourseDuration();
        $goodsNextM = new GoodsNext();
        $crmfenzu = $CrmFenzuM->where('pid',0)->field('id,name')->where('id','not in',[35,36])->select();
//        $manage_arr_id=[];
        $goodsNextId = $goodsNextM->where('goods_id',$query['course_id'])->order('id desc')->column('id');
        foreach ($crmfenzu as &$value){
            $manage_id = $CrmFenzuM->zu_username($value['id']);
//            foreach ($manage_id as $id){
//                array_push($manage_arr_id,$id);
//            }
            $where['public_yingqi']=$query['course_id'];
            $where['manage_id']=['in',$manage_id];
            $res = $crmCustomerM->where($where)->select();
            $value['paike']=count($res);
            $phone_arr = array_column($res,'phone');
            $durations = $courseDurationM->where('course_id','in',$goodsNextId)->where('playStatus','live')->where('phone','in',$phone_arr)->group('phone,course_id')->field('phone,course_id,count(*) as count,SUM(playDuration) as play')->select();
            $d =$courseDurationM->where('course_id','in',$goodsNextId)->where('playStatus','live')->where('phone','in',$phone_arr)->group('phone')->count();
            $value = $this->paike($value,$durations,$goodsNextId,$d);
            $value['course_id']=$query['course_id'];
        }
        $wheres['public_yingqi']=$query['course_id'];
        //用作统计不到的数据，如果存在说明教务数据错乱
        $jiaowu_manage_id_count_null= $crmCustomerM->where($wheres)->where("manage_id is null or manage_id = ''")->field('jiaowu_manage_id')->count();
        apiReturn(200,'成功',['list'=>$crmfenzu,'jiaowu_manage_id_count_null'=>$jiaowu_manage_id_count_null]);
    }

    public function paike(&$value,$durations,$goodsNextId,$d){
        $value['not_go'] = $value['paike']-$d;
        if($value['not_go']<0){
            $value['not_go'] = abs($value['not_go']);
            $value['paike']+=$value['not_go'];
            $value['not_go'] = $value['paike']-$d;
        }
        $value['first_have_go']=0;
        $value['first_have_go_rate']="0%";
        $value['first_status1']=0;
        $value['first_status2']=0;
        $value['first_status3']=0;
        $value['first_status4']=0;
        $value['first_status5']=0;
        $value['first_status6']=0;
        $value['two_have_go']=0;
        $value['two_have_go_rate']="0%";
        $value['two_status1']=0;
        $value['two_status2']=0;
        $value['two_status3']=0;
        $value['two_status4']=0;
        $value['two_status5']=0;
        $value['two_status6']=0;
        foreach ($durations as $val){
            $status=$this->durations($val['play']);
            if($goodsNextId[1]==$val['course_id']){
                $value['first_have_go']+=1;
                if($status<=1){
                    $value['first_status1']+=1;
                }elseif ($status==2){
                    $value['first_status2']+=1;
                }elseif ($status==3){
                    $value['first_status3']+=1;
                }elseif ($status==4){
                    $value['first_status4']+=1;
                }elseif ($status==5){
                    $value['first_status5']+=1;
                }
            }elseif ($goodsNextId[0]==$val['course_id']){
                $value['two_have_go']+=1;
                if($status<=1){
                    $value['two_status1']+=1;
                }elseif ($status==2){
                    $value['two_status2']+=1;
                }elseif ($status==3){
                    $value['two_status3']+=1;
                }elseif ($status==4){
                    $value['two_status4']+=1;
                }elseif ($status==5){
                    $value['two_status5']+=1;
                }
            }
        }
        if($value['paike']>0){
            $value['first_have_go_rate']=(round(($value['first_have_go']/$value['paike']),4)*100).'%';
            $value['two_have_go_rate'] = (round(($value['two_have_go']/$value['paike']),4)*100).'%';
        }
        return $value;
    }

    public function publicCourseDetails(){
        $query = input();
        if(empty($query['course_id']))apiReturn(4000,'缺少参数');
        if(empty($query['zu_id']))apiReturn(4000,'缺少参数');
        $CrmFenzuM =  new CrmFenzu();
        $crmCustomerM = new CrmCustomer();
        $courseDurationM = new CrmCourseDuration();
        $goodsNextM = new GoodsNext();
        $manage_id = $CrmFenzuM->zu_username($query['zu_id']);
        $where['c.public_yingqi']=$query['course_id'];
        $where['c.manage_id']=['in',$manage_id];
        $res = $crmCustomerM->alias('c')->join('crm_manage m','c.manage_id=m.username')->where($where)->group('c.manage_id')->field('m.name as manage_name,c.manage_id,count(*) as paike')->select();
        $goodsNextId = $goodsNextM->where('goods_id',$query['course_id'])->order('id desc')->column('id');
        $where_manage['public_yingqi']=$query['course_id'];
        foreach ($res as &$value){
            $where_manage['manage_id']=$value['manage_id'];
            $phone = $crmCustomerM->where($where_manage)->column('phone');
            $durations = $courseDurationM->where('course_id','in',$goodsNextId)->where('playStatus','live')->where('phone','in',$phone)->group('phone,course_id')->field('phone,course_id,count(*) as count,SUM(playDuration) as play')->select();
            $d =$courseDurationM->where('course_id','in',$goodsNextId)->where('playStatus','live')->where('phone','in',$phone)->group('phone')->count();
            $value = $this->paike($value,$durations,$goodsNextId,$d);
        }
//        //是该组邀约但不是该组归属
//
//
//        //无邀约或不是该组邀约
//        $sql="SELECT `client_id`,`name`,`manage_id`,`yaoyue_manage_id` FROM `tp_crm_customer` WHERE  `public_yingqi` =".$query['course_id'];
//        if(count($manage_id)){
//            $sql.=" AND `manage_id` IN (".implode(',',$manage_id).")";
//        }
//        if(count($res)){
//            $sql.= " AND ( `yaoyue_manage_id` NOT IN (".implode(',',array_column($res,'yaoyue_manage_id')).") or `yaoyue_manage_id` is null )";
//        }
//        $ress = Db::query($sql);
        apiReturn(200,'成功',['list'=>$res]);
    }

    public function status($value){


        return $value;
    }

    // 公开课下单时段统计
    public function publicCourseOrderCount()
    {
        $data = input();//token date日期(2023-05-01) course_id公开课营期id

        if (empty($data['date'])) {
            apiReturn(201,'日期不能为空');
        }else{
            $date = explode('T', $data['date']);
            $data['date'] = $date[0];
        }
        $source = [];
        if (isset($data['source'])) {
            if (!empty($data['source'])) {
                $source = ['in',implode(',', $data['source'])];
            }
        }
        $yingqi = [];
        if (isset($data['course_id'])) {
            if (!empty($data['course_id'])) {
                $yingqi = ['in',implode(',', $data['course_id'])];
            }
        }
        
        $data1['starttime'] = $data['date']." 20:00:00";
        $data1['endtime'] = $data['date']." 20:10:00";
        $where1['date'] = array(array('egt',strtotime($data1['starttime'])),array('lt',strtotime($data1['endtime'])));

        $data2['starttime'] = $data['date']." 20:10:00";
        $data2['endtime'] = $data['date']." 20:20:00";
        $where2['date'] = array(array('egt',strtotime($data2['starttime'])),array('lt',strtotime($data2['endtime'])));

        $data3['starttime'] = $data['date']." 20:20:00";
        $data3['endtime'] = $data['date']." 20:30:00";
        $where3['date'] = array(array('egt',strtotime($data3['starttime'])),array('lt',strtotime($data3['endtime'])));

        $data4['starttime'] = $data['date']." 20:30:00";
        $data4['endtime'] = $data['date']." 20:40:00";
        $where4['date'] = array(array('egt',strtotime($data4['starttime'])),array('lt',strtotime($data4['endtime'])));

        $data5['starttime'] = $data['date']." 20:40:00";
        $data5['endtime'] = $data['date']." 20:50:00";
        $where5['date'] = array(array('egt',strtotime($data5['starttime'])),array('lt',strtotime($data5['endtime'])));

        $data6['starttime'] = $data['date']." 20:50:00";
        $data6['endtime'] = $data['date']." 21:00:00";
        $where6['date'] = array(array('egt',strtotime($data6['starttime'])),array('lt',strtotime($data6['endtime'])));

        $data7['starttime'] = $data['date']." 21:00:00";
        $data7['endtime'] = $data['date']." 21:10:00";
        $where7['date'] = array(array('egt',strtotime($data7['starttime'])),array('lt',strtotime($data7['endtime'])));

        $data8['starttime'] = $data['date']." 21:10:00";
        $data8['endtime'] = $data['date']." 21:20:00";
        $where8['date'] = array(array('egt',strtotime($data8['starttime'])),array('elt',strtotime($data8['endtime'])));

        $data9['starttime'] = $data['date']." 19:50:00";
        $data9['endtime'] = $data['date']." 20:00:00";
        $where9['date'] = array(array('egt',strtotime($data9['starttime'])),array('elt',strtotime($data9['endtime'])));

        $datas[0]['time'] = "20:00-20:10";
        if (empty($source) && empty($yingqi)) {
            $datas[0]['zhifu_di'] = db('cooperation')->where(array('date'=>$where1['date'],'number'=>2980,'grade'=>array('in',"1年级,2年级,3年级")))->count();
            $datas[0]['zhifu_gao'] = db('cooperation')->where(array('date'=>$where1['date'],'number'=>2980,'grade'=>array('in',"4年级,5年级,6年级,初一年级,初二年级,初三年级")))->count();
        }else{
            $where_time1 = ['a.date'=>$where1['date'],'a.number'=>2980];
            if (!empty($source)) {
                $where_time1['b.source'] = $source;
            }
            if (!empty($yingqi)) {
                $where_time1['b.public_yingqi'] = $yingqi;
            }
            $where_time1_di = $where_time1;
            $where_time1_di['a.grade'] = ['in','1年级,2年级,3年级'];
            $where_time1_gao = $where_time1;
            $where_time1_gao['a.grade'] = ['in','4年级,5年级,6年级,初一年级,初二年级,初三年级'];
            $datas[0]['zhifu_di'] = db('cooperation')->alias('a')->join('tp_crm_customer b','a.phone=b.phone')->where($where_time1_di)->count();
            $datas[0]['zhifu_gao'] = db('cooperation')->alias('a')->join('tp_crm_customer b','a.phone=b.phone')->where($where_time1_gao)->count();
        }

        $datas[1]['time'] = "20:10-20:20";
        if (empty($source) && empty($yingqi)) {
            $datas[1]['zhifu_di'] = db('cooperation')->where(array('date'=>$where2['date'],'number'=>2980,'grade'=>array('in',"1年级,2年级,3年级")))->count();
            $datas[1]['zhifu_gao'] = db('cooperation')->where(array('date'=>$where2['date'],'number'=>2980,'grade'=>array('in',"4年级,5年级,6年级,初一年级,初二年级,初三年级")))->count();
        }else{
            $where_time2 = ['a.date'=>$where2['date'],'a.number'=>2980];
            if (!empty($source)) {
                $where_time2['b.source'] = $source;
            }
            if (!empty($yingqi)) {
                $where_time2['b.public_yingqi'] = $yingqi;
            }
            $where_time2_di = $where_time2;
            $where_time2_di['a.grade'] = ['in','1年级,2年级,3年级'];
            $where_time2_gao = $where_time2;
            $where_time2_gao['a.grade'] = ['in','4年级,5年级,6年级,初一年级,初二年级,初三年级'];
            $datas[1]['zhifu_di'] = db('cooperation')->alias('a')->join('tp_crm_customer b','a.phone=b.phone')->where($where_time2_di)->count();
            $datas[1]['zhifu_gao'] = db('cooperation')->alias('a')->join('tp_crm_customer b','a.phone=b.phone')->where($where_time2_gao)->count();
        }

        $datas[2]['time'] = "20:20-20:30";
        if (empty($source) && empty($yingqi)) {
            $datas[2]['zhifu_di'] = db('cooperation')->where(array('date'=>$where3['date'],'number'=>2980,'grade'=>array('in',"1年级,2年级,3年级")))->count();
            $datas[2]['zhifu_gao'] = db('cooperation')->where(array('date'=>$where3['date'],'number'=>2980,'grade'=>array('in',"4年级,5年级,6年级,初一年级,初二年级,初三年级")))->count();
        }else{
            $where_time3 = ['a.date'=>$where3['date'],'a.number'=>2980];
            if (!empty($source)) {
                $where_time3['b.source'] = $source;
            }
            if (!empty($yingqi)) {
                $where_time3['b.public_yingqi'] = $yingqi;
            }
            $where_time3_di = $where_time3;
            $where_time3_di['a.grade'] = ['in','1年级,2年级,3年级'];
            $where_time3_gao = $where_time3;
            $where_time3_gao['a.grade'] = ['in','4年级,5年级,6年级,初一年级,初二年级,初三年级'];
            $datas[2]['zhifu_di'] = db('cooperation')->alias('a')->join('tp_crm_customer b','a.phone=b.phone')->where($where_time3_di)->count();
            $datas[2]['zhifu_gao'] = db('cooperation')->alias('a')->join('tp_crm_customer b','a.phone=b.phone')->where($where_time3_gao)->count();
        }

        $datas[3]['time'] = "20:30-20:40";
        if (empty($source) && empty($yingqi)) {
            $datas[3]['zhifu_di'] = db('cooperation')->where(array('date'=>$where4['date'],'number'=>2980,'grade'=>array('in',"1年级,2年级,3年级")))->count();
            $datas[3]['zhifu_gao'] = db('cooperation')->where(array('date'=>$where4['date'],'number'=>2980,'grade'=>array('in',"4年级,5年级,6年级,初一年级,初二年级,初三年级")))->count();
        }else{
            $where_time4 = ['a.date'=>$where4['date'],'a.number'=>2980];
            if (!empty($source)) {
                $where_time4['b.source'] = $source;
            }
            if (!empty($yingqi)) {
                $where_time4['b.public_yingqi'] = $yingqi;
            }
            $where_time4_di = $where_time4;
            $where_time4_di['a.grade'] = ['in','1年级,2年级,3年级'];
            $where_time4_gao = $where_time4;
            $where_time4_gao['a.grade'] = ['in','4年级,5年级,6年级,初一年级,初二年级,初三年级'];
            $datas[3]['zhifu_di'] = db('cooperation')->alias('a')->join('tp_crm_customer b','a.phone=b.phone')->where($where_time4_di)->count();
            $datas[3]['zhifu_gao'] = db('cooperation')->alias('a')->join('tp_crm_customer b','a.phone=b.phone')->where($where_time4_gao)->count();
        }

        $datas[4]['time'] = "20:40-20:50";
        if (empty($source) && empty($yingqi)) {
            $datas[4]['zhifu_di'] = db('cooperation')->where(array('date'=>$where5['date'],'number'=>2980,'grade'=>array('in',"1年级,2年级,3年级")))->count();
            $datas[4]['zhifu_gao'] = db('cooperation')->where(array('date'=>$where5['date'],'number'=>2980,'grade'=>array('in',"4年级,5年级,6年级,初一年级,初二年级,初三年级")))->count();
        }else{
            $where_time5 = ['a.date'=>$where5['date'],'a.number'=>2980];
            if (!empty($source)) {
                $where_time5['b.source'] = $source;
            }
            if (!empty($yingqi)) {
                $where_time5['b.public_yingqi'] = $yingqi;
            }
            $where_time5_di = $where_time5;
            $where_time5_di['a.grade'] = ['in','1年级,2年级,3年级'];
            $where_time5_gao = $where_time5;
            $where_time5_gao['a.grade'] = ['in','4年级,5年级,6年级,初一年级,初二年级,初三年级'];
            $datas[4]['zhifu_di'] = db('cooperation')->alias('a')->join('tp_crm_customer b','a.phone=b.phone')->where($where_time5_di)->count();
            $datas[4]['zhifu_gao'] = db('cooperation')->alias('a')->join('tp_crm_customer b','a.phone=b.phone')->where($where_time5_gao)->count();
        }

        $datas[5]['time'] = "20:50-21:00";
        if (empty($source) && empty($yingqi)) {
            $datas[5]['zhifu_di'] = db('cooperation')->where(array('date'=>$where6['date'],'number'=>2980,'grade'=>array('in',"1年级,2年级,3年级")))->count();
            $datas[5]['zhifu_gao'] = db('cooperation')->where(array('date'=>$where6['date'],'number'=>2980,'grade'=>array('in',"4年级,5年级,6年级,初一年级,初二年级,初三年级")))->count();
        }else{
            $where_time6 = ['a.date'=>$where6['date'],'a.number'=>2980];
            if (!empty($source)) {
                $where_time6['b.source'] = $source;
            }
            if (!empty($yingqi)) {
                $where_time6['b.public_yingqi'] = $yingqi;
            }
            $where_time6_di = $where_time6;
            $where_time6_di['a.grade'] = ['in','1年级,2年级,3年级'];
            $where_time6_gao = $where_time6;
            $where_time6_gao['a.grade'] = ['in','4年级,5年级,6年级,初一年级,初二年级,初三年级'];
            $datas[5]['zhifu_di'] = db('cooperation')->alias('a')->join('tp_crm_customer b','a.phone=b.phone')->where($where_time6_di)->count();
            $datas[5]['zhifu_gao'] = db('cooperation')->alias('a')->join('tp_crm_customer b','a.phone=b.phone')->where($where_time6_gao)->count();
        }

        $datas[6]['time'] = "21:00-21:10";
        if (empty($source) && empty($yingqi)) {
            $datas[6]['zhifu_di'] = db('cooperation')->where(array('date'=>$where7['date'],'number'=>2980,'grade'=>array('in',"1年级,2年级,3年级")))->count();
            $datas[6]['zhifu_gao'] = db('cooperation')->where(array('date'=>$where7['date'],'number'=>2980,'grade'=>array('in',"4年级,5年级,6年级,初一年级,初二年级,初三年级")))->count();
        }else{
            $where_time7 = ['a.date'=>$where7['date'],'a.number'=>2980];
            if (!empty($source)) {
                $where_time7['b.source'] = $source;
            }
            if (!empty($yingqi)) {
                $where_time7['b.public_yingqi'] = $yingqi;
            }
            $where_time7_di = $where_time7;
            $where_time7_di['a.grade'] = ['in','1年级,2年级,3年级'];
            $where_time7_gao = $where_time7;
            $where_time7_gao['a.grade'] = ['in','4年级,5年级,6年级,初一年级,初二年级,初三年级'];
            $datas[6]['zhifu_di'] = db('cooperation')->alias('a')->join('tp_crm_customer b','a.phone=b.phone')->where($where_time7_di)->count();
            $datas[6]['zhifu_gao'] = db('cooperation')->alias('a')->join('tp_crm_customer b','a.phone=b.phone')->where($where_time7_gao)->count();
        }

        $datas[7]['time'] = "21:10-21:20";
        if (empty($source) && empty($yingqi)) {
            $datas[7]['zhifu_di'] = db('cooperation')->where(array('date'=>$where8['date'],'number'=>2980,'grade'=>array('in',"1年级,2年级,3年级")))->count();
            $datas[7]['zhifu_gao'] = db('cooperation')->where(array('date'=>$where8['date'],'number'=>2980,'grade'=>array('in',"4年级,5年级,6年级,初一年级,初二年级,初三年级")))->count();
        }else{
            $where_time8 = ['a.date'=>$where8['date'],'a.number'=>2980];
            if (!empty($source)) {
                $where_time8['b.source'] = $source;
            }
            if (!empty($yingqi)) {
                $where_time8['b.public_yingqi'] = $yingqi;
            }
            $where_time8_di = $where_time8;
            $where_time8_di['a.grade'] = ['in','1年级,2年级,3年级'];
            $where_time8_gao = $where_time8;
            $where_time8_gao['a.grade'] = ['in','4年级,5年级,6年级,初一年级,初二年级,初三年级'];
            $datas[7]['zhifu_di'] = db('cooperation')->alias('a')->join('tp_crm_customer b','a.phone=b.phone')->where($where_time8_di)->count();
            $datas[7]['zhifu_gao'] = db('cooperation')->alias('a')->join('tp_crm_customer b','a.phone=b.phone')->where($where_time8_gao)->count();
        }

        $datas[8]['time'] = "19:50-20:00";
        if (empty($source) && empty($yingqi)) {
            $datas[8]['zhifu_di'] = db('cooperation')->where(array('date'=>$where9['date'],'number'=>2980,'grade'=>array('in',"1年级,2年级,3年级")))->count();
            $datas[8]['zhifu_gao'] = db('cooperation')->where(array('date'=>$where9['date'],'number'=>2980,'grade'=>array('in',"4年级,5年级,6年级,初一年级,初二年级,初三年级")))->count();
        }else{
            $where_time9 = ['a.date'=>$where9['date'],'a.number'=>2980];
            if (!empty($source)) {
                $where_time9['b.source'] = $source;
            }
            if (!empty($yingqi)) {
                $where_time9['b.public_yingqi'] = $yingqi;
            }
            $where_time9_di = $where_time9;
            $where_time9_di['a.grade'] = ['in','1年级,2年级,3年级'];
            $where_time9_gao = $where_time9;
            $where_time9_gao['a.grade'] = ['in','4年级,5年级,6年级,初一年级,初二年级,初三年级'];
            $datas[8]['zhifu_di'] = db('cooperation')->alias('a')->join('tp_crm_customer b','a.phone=b.phone')->where($where_time9_di)->count();
            $datas[8]['zhifu_gao'] = db('cooperation')->alias('a')->join('tp_crm_customer b','a.phone=b.phone')->where($where_time9_gao)->count();
        }

        $datas1 = [$datas[8],$datas[0],$datas[1],$datas[2],$datas[3],$datas[4],$datas[5],$datas[6],$datas[7]];

        apiReturn(200,'获取公开课下单时段统计',$datas1);
    }

    /*
     *数据分配统计
     *  */
    public function customerFenpeiCount()
    {
        $data = input();//token date(2022-05-01) zu_id

        if (empty($data['date'])) {
            apiReturn(201,'时间不能为空');
        }

        $data['starttime'] = $data['date']." 20:00:00";
        $data['endtime'] = $data['date']." 20:10:00";
        $where['zhuanyi_date'] = array(array('egt',strtotime($data['starttime'])),array('lt',strtotime($data['endtime'])));

        $wherezu_id = [];
        $manage_author = $this->getManageAuthor();
        if ($manage_author['is_xiaoshou'] == 1) {
            $where['manage_id'] = $this->getManageList();
            $wherezu_id = $this->getFenzuList();
        }
        if (isset($data['zu_id'])) {
            if (!empty($data['zu_id'])) {
                $wherezu_id = $this->getFenzuList($data['zu_id']);
                $where['manage_id'] = $this->getManageList($wherezu_id);
            }
        }

        $fenzuList = db('crm_fenzu')->field('id,name')->where($wherezu_id)->order('id ASC')->select();
        foreach ($fenzuList as $key => $value) {
            $fenpei = 0;//分配数
            $wuxiao = 0;//无效数
            $tuikuan = 0;//退款数
            $tuikuan_rate = 0;//退款率
            $shiji = 0;//实际数

            $fenzuList[$key]['fenpei'] = db('crm_customer')->where($where)->count();
            $where1 = $where;
            $where1['source'] = '39元公开课未付款';
            $where1['become_zhifu'] = 0;
            $fenzuList[$key]['wuxiao'] = db('crm_customer')->where($where1)->count();
            $shiji = $fenzuList[$key]['fenpei'] - $fenzuList[$key]['wuxiao'];
            $fenzuList[$key]['shiji'] = $shiji;
            $where2 = $where;
            $where2['is_refund'] = 1;
            $fenzuList[$key]['tuikuan'] = db('crm_customer')->where($where1)->count();
            if (!empty($shiji)) {
                $fenzuList[$key]['tuikuan_rate'] = intval(sprintf("%.2f",($fenzuList[$key]['tuikuan'])/$shiji)*100);
            }else{
                $fenzuList[$key]['tuikuan_rate'] = 0;
            }
        }
        
        apiReturn(200,'获取数据分配统计成功',$fenzuList);
    }


    public function customerFenpeiCount2(){
        $query = input();
        $query['limit'] = isset($query['limit'])?$query['limit']:10;
        $query['page'] = isset($query['page'])?$query['page']:1;
        if(isset($query['endtime']) && isset($query['starttime'])){
            if($query['starttime']>$query['endtime'])apiReturn(400,'查询开始日期不能大于结束日期');
        }
        if(empty($query['starttime'])|| empty($query['endtime'])){
            $query['starttime'] = date('Y-m-d H:i:s',mktime(0, 0, 0, date('m'), date('d'), date('Y'))) ;
            $query['endtime'] =date('Y-m-d H:i:s',mktime(23, 59, 59, date('m'), date('d'), date('Y'))) ;
        }
        $where['date']=['between',[strtotime($query['starttime']),strtotime($query['endtime'])]];
        $CrmCustomerZhuanyiM =new CrmCustomerZhuanyi();
        $totals = $this->customerZhuanyi($CrmCustomerZhuanyiM,$query)->count();
        $res = $this->customerZhuanyi($CrmCustomerZhuanyiM,$query)->field('c.manage_id,m.name,m.zu_id,f.name as zu_name,count(*) as count')->limit($query['limit'])->page($query['page'])->select();
        $data['total_amount']=0;
        $data['total_refund_amount']=0;
        $data['total_refund_rate']='0%';
        $data['total_conversion'] = 0;
        $data['total_conversion_rate']='0%';
        $data['wuxiao_amount'] = 0;
        $data['youxiao_amount'] = 0;
        $data['price']=0;
        $price_total=0;
        $crmCustomerM = new CrmCustomer();
        foreach ($res as &$value){
            if(!is_object($value)){
                continue;
            }
            $term['manage_id']=$value['manage_id'];
            $term['date']=$where['date'];
            $manage_client = $CrmCustomerZhuanyiM->where($term)->column('client_id');
            $customer_amount = $crmCustomerM->where(['client_id'=>['in',$manage_client],'source'=>['not like','%未付款']])->field('id,is_refund,yaoyue_status,public_paytype,client_id')->select();
            //$value['customer_id']=array_column($customer_amount,'id');
            $value['manage_zu_id']=$value['manage_id'];
            $value['date']=[strtotime($query['starttime']),strtotime($query['endtime'])];
            $value['amount']= $amount = count($customer_amount);
            $value['refund_amount']=0;
            $yaoyue_status = 0;
            $conversion_select = 0;
            foreach ($customer_amount as $item){
                if($item->is_refund==1){
                    $value['refund_amount']+=1;
                }
                if($item->yaoyue_status==5 || $item->yaoyue_status==2){
                    $yaoyue_status+=1;
                }
                if($item->public_paytype==2 || $item->public_paytype==3){
                    $conversion_select+=1;
                }
            }
            $value['wuxiao']=$yaoyue_wuxiao =   $value['refund_amount']+ $yaoyue_status;
            $customer_become_zhifu = array_column($crmCustomerM->customer_select(['client_id'=>['in',$manage_client]]),'id');
            $value['youxiao']=$value['amount'] - $value['wuxiao']+$crmCustomerM->customer_count(array_merge(['id'=>['in',$customer_become_zhifu]],['become_zhifu'=>1]));//已经支付-退款-空号且已支付+转支付=有效数
            $value['conversion'] = $conversion_select; //count($conversion_select);
            $value['price']=0;
            if($value['youxiao']>0){
                $total =  $value['conversion']*2980;
                $price_total+=$total;
                $value['price'] = round($total/$value['youxiao'],2);
            }
            $value['refund_amount_rate']=0;
            $value['refund_amount_rate']='0%';
            $value['conversion_rate']='0%';
            if($value['amount']>0){
                $value['refund_amount_rate']=(round(($value['refund_amount']/$value['amount']),4)*100).'%';
            }

            if($value['youxiao']>0){
                $value['conversion_rate']=(round(($value['conversion']/$value['youxiao']),4)*100).'%';
            }
            $data['total_amount']+=$value['amount'];
            $data['total_refund_amount']+=$value['refund_amount'];
            $data['total_conversion']+=$value['conversion'];
            $data['wuxiao_amount']+= $yaoyue_wuxiao;
            $data['youxiao_amount'] += $value['youxiao'];
        }
        if($data['total_amount']>0){
            $data['total_refund_rate'] = (round(($data['total_refund_amount']/$data['total_amount']),4)*100).'%';
        }
        if($data['youxiao_amount']>0){
            $data['total_conversion_rate'] = (round(($data['total_conversion']/$data['youxiao_amount']),4)*100).'%';
            $data['price'] = round(( $price_total/$data['youxiao_amount']),2);
        }
        $data['list']=$res;
        $data['total']=$totals;
        apiReturn(200,'成功',$data);
    }

    public function customerFenpeiCountDetail(){
        $query = input();
        if(empty($query['manage_zu_id']))apiReturn(4000,'缺少参数');
        if(empty($query['date']))apiReturn(4000,'缺少参数');
        $costomerM =new CrmCustomer();
        $manageM = new CrmManage();
        $CrmCustomerZhuanyiM =new CrmCustomerZhuanyi();
        $term['date']=['between',$query['date']];
        $term['manage_id']=$query['manage_zu_id'];
        $client_id = $CrmCustomerZhuanyiM->where($term)->column('client_id');
        $manage_id = $costomerM->where('client_id','in',$client_id)->group('manage_id')->column('manage_id');
        $manage = $manageM->where('username','in',$manage_id)->field('username,name')->select();
        foreach ($manage as &$value){
            $yaoyue_status = 0;
            $conversion = 0;
            $value['conversion']=0;
            $value['refund_amount']=0;
            $where['client_id']=['in',$client_id ];
            $where['manage_id']=$value['username'];
            $where['source']=['not like','%未付款'];
            $res = $costomerM->where($where)->field('id,is_refund,yaoyue_status,public_paytype,client_id')->select();
            $value['amount']=count($res);
            foreach ($res as $itme){
                if($itme->is_refund==1){
                    $value['refund_amount']+=1;
                }
                if($itme->yaoyue_status==5 || $itme->yaoyue_status==2){
                    $yaoyue_status+=1;
                }
                if($itme->public_paytype==2 || $itme->public_paytype==3){
                    $conversion+=1;
                }
            }
            $value['refund_amount_rate']='0%';
            if($value['amount']>0){
                $value['refund_amount_rate'] =  (round(($value['refund_amount']/$value['amount']),4)*100).'%';;
            }
            $value['wuxiao'] =  $value['refund_amount']+ $yaoyue_status;
            $customer_become_zhifu = array_column($costomerM->customer_select(['client_id'=>['in',$client_id]]),'id');
            $value['youxiao']=$value['amount'] - $value['wuxiao']+$costomerM->customer_count(array_merge(['id'=>['in',$customer_become_zhifu]],['become_zhifu'=>1]));//已经支付-退款-空号且已支付+转支付=有效数
            $value['youxiao'] = $value['amount']-$value['wuxiao'];
            $value['conversion']=$conversion;
        }
        apiReturn(200,'成功',$manage);
    }

    public function customerZhuanyi($CrmCustomerZhuanyiM,$query){
        $CrmCustomerZhuanyiM->alias('c')->join('crm_manage m','c.manage_id=m.username')->join('crm_fenzu f','m.zu_id=f.id','left');
        if(!empty($query['endtime']) && !empty($query['starttime'])){
            if($query['starttime']>$query['endtime'])apiReturn(400,'查询开始日期不能大于结束日期');
            $where['c.date']=['between',[strtotime($query['starttime']),strtotime($query['endtime'])]];
            $CrmCustomerZhuanyiM ->where($where);
        }
        if(isset($query['zu_id'])){
            $zu_arr = (new CrmFenzu())->fenzu($query['zu_id']);
            array_push($zu_arr,(int)$query['zu_id']);
            $CrmCustomerZhuanyiM->where('m.zu_id','in',$zu_arr);
        }
        $res = $CrmCustomerZhuanyiM->group('c.manage_id');
        return $res;
    }
























    /*
     *员工业绩统计
     *  */
    public function staffCount()
    {
        $data = input();//token zu_id starttime endtime

        $manage_author = self::getManageAuthor();
        
        $where = [];
        $where1 = [];
        if ($manage_author['is_xiaoshou'] == 1) {
            $where['manage_id'] = $this->getManageList();
        }
        $where1['is_xiaoshou'] = 1;

        if (isset($data['zu_id'])) {
            if (!empty($data['zu_id'])) {
                $wherezu_id = $this->getFenzuList($data['zu_id']);
                $where['manage_id'] = $this->getManageList($wherezu_id);
            }
        }
        if (isset($data['starttime']) && isset($data['endtime'])) {
            if (!empty($data['starttime']) && !empty($data['endtime'])) {
                $where['date'] = array(array('egt',strtotime($data['starttime'])),array('elt',strtotime($data['endtime'])));
            }else{
                apiReturn(201,'时间不能为空');
            }
        }else{
            apiReturn(201,'时间不能为空');
        }

        $where['order_status'] = array('notin','1,7,8');
        $where['check_status'] = array('in','3,4');//财务审核后才算业绩
        $where['total'] = array('>',0);
        //退款金额弄一个界限，超过界限才算订单数和业绩

        $manageRankList = db('crm_order')->field('id,manage_id,count(id) as number,sum(total) as money')->where($where)->group('manage_id')->order('money desc')->select();
        $where1 = $where;
        $where1['order_status'] = array('notin','1,6,7,8');
        $where1['total'] = array('>=',2900);
        $manageRankList1 = db('crm_order')->field('id,manage_id,count(id) as number1,sum(total) as money')->where($where1)->group('manage_id')->order('money desc')->select();
        // var_dump($manageRankList);
        // var_dump($manageRankList1);exit;
        $type = 0;
        if (isset($data['type'])) {
            if (!empty($data['type'])) {
                $type = $data['type'];
            }
        }
        foreach ($manageRankList as $key => $value) {
            $manageInfo = db('crm_manage')->alias('a')->join('tp_crm_fenzu b','a.zu_id=b.id')->field('a.name,a.type,b.name as fenzu_name')->where('a.username',$value['manage_id'])->find();
            $manageRankList[$key]['name'] = $manageInfo['name'];
            $manageRankList[$key]['fenzu_name'] = $manageInfo['fenzu_name'];
            $manageRankList[$key]['money'] = intval($value['money']);
            $manage_id_key = array_search($value['manage_id'], array_column($manageRankList1, 'manage_id'));
            if ($manage_id_key === false) {
                $manageRankList[$key]['number1'] = 0;
            }else{
                $manageRankList[$key]['number1'] = $manageRankList1[$manage_id_key]['number1'];
            }
            $manageRankList[$key]['number2'] = $value['number'] - $manageRankList[$key]['number1'];
            if ($manageInfo['type'] == 1) {
                // var_dump($manageRankList[$key]);exit;
                unset($manageRankList[$key]);
            }
            
            if (!empty($type)) {
                if ($manageInfo['type'] != $type) {
                    unset($manageRankList[$key]);
                }
            }
        }
        $list = [];
        foreach ($manageRankList as $key => $value) {
            $list[] = $value;
        }


        // $manageList = db('crm_manage')->field('id,username,name,fenzu_name')->where($where1)->order('zu_id ASC,id ASC')->select();

        // foreach ($manageList as $key => $value) {
        //     $where['manage_id'] = $value['username'];
        //     $manageOrders = db('crm_order_money')->where($where)->select();

        //     $order_number = 0;//下单数量
        //     $order_totals = 0;//下单金额
        //     $qianshou_number = 0;//签收数量
        //     $qianshou_totals = 0;//签收金额
        //     $qianshou_rate = 0;//签收率
        //     foreach ($manageOrders as $key1 => $value1) {
        //         $order_number++;
        //         $order_totals += $value1['money'];
        //         if ($value1['order_status'] == 5) {
        //             $qianshou_number++;
        //             $qianshou_totals += $value1['money'];
        //         }
        //     }
            
        //     $qianshou_rate = !empty($fahuo_number) ? sprintf("%.2f", $qianshou_number/$fahuo_number) : 0;

        //     $manageList[$key]['order_number'] = $order_number;
        //     $manageList[$key]['order_totals'] = $order_totals;
        //     $manageList[$key]['qianshou_number'] = $qianshou_number;
        //     $manageList[$key]['qianshou_totals'] = $qianshou_totals;
        //     $manageList[$key]['qianshou_rate'] = $qianshou_rate;
        // }

        apiReturn(200,'获取员工业绩成功',$list);
    }

    /*
     *分组业绩统计
     *  */
    public function fenzuCount()
    {
        $data = input();//token zu_id starttime endtime

        $manage_author = self::getManageAuthor();
        
        $where = [];
        $where1 = [];
        if ($manage_author['is_xiaoshou'] == 1) {
            $where1['id'] = $this->getFenzuList();
        }
        $where1['is_xiaoshou'] = 1;
        if (isset($data['zu_id'])) {
            if (!empty($data['zu_id'])) {
                $where1['id'] = $this->getFenzuList($data['zu_id']);
            }
        }
        if (isset($data['starttime']) && isset($data['endtime'])) {
            if (!empty($data['starttime']) && !empty($data['endtime'])) {
                $where['date'] = array(array('egt',strtotime($data['starttime'])),array('elt',strtotime($data['endtime'])));
            }else{
                apiReturn(201,'时间不能为空');
            }
        }else{
            apiReturn(201,'时间不能为空');
        }

        $fenzuList = db('crm_fenzu')->field('id,name,pid')->where($where1)->order('id ASC')->select();

        foreach ($fenzuList as $key => $value) {
            $where['zu_id'] = $this->getFenzuList($value['id']);
            $zuOrders = db('crm_order_money')->where($where)->select();
            $order_number = 0;//下单数量
            $order_totals = 0;//下单金额
            $qianshou_number = 0;//签收数量
            $qianshou_totals = 0;//签收金额
            $qianshou_rate = 0;//签收率
            foreach ($zuOrders as $key1 => $value1) {
                $order_number++;
                $order_totals += $value1['money'];
                if ($value1['order_status'] == 5) {
                    $qianshou_number++;
                    $qianshou_totals += $value1['money'];
                }
            }
            $qianshou_rate = !empty($fahuo_number) ? sprintf("%.2f", $qianshou_number/$fahuo_number) : 0;
            $fenzuList[$key]['order_number'] = $order_number;
            $fenzuList[$key]['order_totals'] = $order_totals;
            $fenzuList[$key]['qianshou_number'] = $qianshou_number;
            $fenzuList[$key]['qianshou_totals'] = $qianshou_totals;
            $fenzuList[$key]['qianshou_rate'] = $qianshou_rate;
        }

        apiReturn(200,'获取分组业绩成功',$fenzuList);
    }



















    /*
     *地域统计
     *  */
    public function areaCount()
    {
        $data = input();//token province_id starttime endtime source

        $where['region_type'] = 1;
        if (isset($data['province_id'])) {
            if (!empty($data['province_id'])) {
                $where['parent_id'] = $data['province_id'];
                $where['region_type'] = 2;
            }
        }
        $areaList = db('crm_region')->field('region_id,region_name')->where($where)->select();

        $where1 = [];
        if (isset($data['starttime']) && isset($data['endtime'])) {
            if (!empty($data['starttime']) && !empty($data['endtime'])) {
                $where1['date'] = array(array('egt',strtotime($data['starttime'])),array('elt',strtotime($data['endtime'])));
            }else{
                apiReturn(201,'时间不能为空');
            }
        }else{
            apiReturn(201,'时间不能为空');
        }
        if (isset($data['source'])) {
            if (!empty($data['source'])) {
                $where1['source'] = ['in',$data['source']];
            }
        }

        foreach ($areaList as $key => $value) {
            if ($where['region_type'] == 1) {
                $where1['province'] = $value['region_id'];
            }else{
                $where1['city'] = $value['region_id'];
            }
            $areaCustomers = db('crm_customer')->field('client_id,source,is_refund,public_paytype,become_zhifu,yaoyue_status')->where($where1)->select();
            
            $customer_number = 0;//客户数
            $jinxian_number = 0;//进线数
            $youixao_number = 0;//有效数据
            $wuxiao_number = 0;//无效数据
            $tuikuan_number = 0;//退款数
            $tuikuan_rate = 0;//退款率
            $baoke_number = 0;//报课数
            $zhuanhua_rate = 0;//转化率
            $danfenjia = 0;//单粉价
            $konghao_number = 0;//空号
            $become_zhifu_number = 0;//转支付数量
            
            foreach ($areaCustomers as $key1 => $value1) {
                $customer_number++;
                if (!strpos($value1['source'], '未付款')) {
                    $jinxian_number++;
                    if ($value1['yaoyue_status'] == 5) {
                        $konghao_number++;
                    }
                }
                if ($value1['is_refund'] == 1) {
                    $tuikuan_number++;
                }
                if ($value1['become_zhifu'] == 1) {
                    $become_zhifu_number++;
                }
                if ($value1['public_paytype'] == 2 || $value1['public_paytype'] == 3) {
                    $baoke_number++;
                }
            }
            $wuxiao_number = $konghao_number + $tuikuan_number;
            $youixao_number = $jinxian_number - $wuxiao_number + $become_zhifu_number;

            $areaList[$key]['customer_number'] = $customer_number;
            $areaList[$key]['jinxian_number'] = $jinxian_number;
            $areaList[$key]['wuxiao_number'] = $wuxiao_number;
            $areaList[$key]['youixao_number'] = $youixao_number;
            $areaList[$key]['tuikuan_number'] = $tuikuan_number;
            if (!empty($jinxian_number)) {
                $areaList[$key]['tuikuan_rate'] = intval(sprintf("%.2f",$tuikuan_number/$jinxian_number)*100);
            }else{
                $areaList[$key]['tuikuan_rate'] = 0;
            }
            $areaList[$key]['baoke_number'] = $baoke_number;
            if (!empty($youixao_number)) {
                $areaList[$key]['zhuanhua_rate'] = intval(sprintf("%.2f",$baoke_number/$youixao_number)*100);
            }else{
                $areaList[$key]['zhuanhua_rate'] = 0;
            }
            if (!empty($youixao_number)) {
                $areaList[$key]['danfenjia'] = round((2980*$baoke_number)/$youixao_number);
            }else{
                $areaList[$key]['danfenjia'] = 0;
            }
        }

        apiReturn(200,'获取地域统计列表成功',$areaList);
    }

    /*
     *客户等级分析
     *  */
    public function customerCount()
    {
        $data = input();//token

        $where['type'] = 15;
        $where['status'] = 1;
        $settingList = db('crm_setting')->field('id,name')->where($where)->order('id ASC')->select();

        foreach ($settingList as $key => $value) {
            $settingList[$key]['customer_number'] = db('crm_customer')->where('dengji',$value['id'])->count();
        }

        apiReturn(200,'获取客户等级分析成功',$settingList);
    }

    /*
     *产品销售统计
     *  */
    public function productCount()
    {
        $data = input();//token

        $where = [];
        $productList = db('crm_order_goods')->distinct(true)->field('goods_id')->where($where)->select();

        foreach ($productList as $key => $value) {
            $where = array(
                'b.goods_id'=>$value['goods_id']
            );
            $orders = db('crm_order')->alias('a')->join('tp_crm_order_goods b','a.orderid=b.order_id')->field('a.*')->where($where)->group('a.orderid')->select();

            $numbers = 0;//总件数
            $order_number = 0;//下单数量
            $order_totals = 0;//下单金额
            $shendan_number = 0;//审单数量
            $shendan_totals = 0;//审单金额
            $fahuo_number = 0;//发货数量
            $fahuo_totals = 0;//发货金额
            $qianshou_number = 0;//签收数量
            $qianshou_totals = 0;//签收金额
            $jushou_number = 0;//拒收数量
            $jushou_totals = 0;//拒收金额
            $tuikuan_number = 0;//部分退数量
            $tuikuan_totals = 0;//部分退金额
            $goods_sn = '';//货号
            $goods_name = '';//名称
            $goods_price = 0;//价格
            $numbers = db('crm_order_goods')->where('goods_id',$value['goods_id'])->sum('goods_number');

            foreach ($orders as $key1 => $value1) {
                $order_number++;
                $order_totals += $value1['total'];
                if ($value1['check_status'] == 3) {
                    $shendan_number++;
                    $shendan_totals += $value1['total'];
                }
                if (!empty($value1['fahuo_time'])) {
                    $fahuo_number++;
                    $fahuo_totals += $value1['total'];
                }
                if ($value1['order_status'] == 5) {
                    $qianshou_number++;
                    $qianshou_totals += $value1['total'];
                }
                if ($value1['order_status'] == 7) {
                    $jushou_number++;
                    $jushou_totals += $value1['total'];
                }
                if ($value1['order_status'] == 6) {
                    $tuikuan_number++;
                    $tuikuan_totals += $value1['total'];
                }
            }

            $goods = db('crm_product')->where('sn',$value['goods_id'])->find();

            $productList[$key]['goods_sn'] = $goods['sn'];
            $productList[$key]['goods_name'] = $goods['name'];
            $productList[$key]['goods_price'] = $goods['price'];
            $productList[$key]['numbers'] = $numbers;
            $productList[$key]['order_number'] = $order_number;
            $productList[$key]['order_totals'] = $order_totals;
            $productList[$key]['shendan_number'] = $shendan_number;
            $productList[$key]['shendan_totals'] = $shendan_totals;
            $productList[$key]['fahuo_number'] = $fahuo_number;
            $productList[$key]['fahuo_totals'] = $fahuo_totals;
            $productList[$key]['qianshou_number'] = $qianshou_number;
            $productList[$key]['qianshou_totals'] = $qianshou_totals;
            $productList[$key]['jushou_number'] = $jushou_number;
            $productList[$key]['jushou_totals'] = $jushou_totals;
            $productList[$key]['tuikuan_number'] = $tuikuan_number;
            $productList[$key]['tuikuan_totals'] = $tuikuan_totals;
        }

        apiReturn(200,'获取产品销售统计成功',$productList);
    }

    /*
     *工号客户数统计
     *  */
    public function manageCustomerCount()
    {
        $data = input();//token zu_id

        $manage_author = self::getManageAuthor();
        
        $where = [];
        if ($manage_author['is_xiaoshou'] == 1) {
            $where['username'] = $this->getManageList();
        }
        if (isset($data['zu_id'])) {
            if (!empty($data['zu_id'])) {
                $wherezu_id = $this->getFenzuList($data['zu_id']);
                $where['username'] = $this->getManageList($wherezu_id);
            }
        }
        if (isset($data['starttime']) && isset($data['endtime'])) {
            if (!empty($data['starttime']) && !empty($data['endtime'])) {
                $where1['date'] = array(array('egt',strtotime($data['starttime'])),array('elt',strtotime($data['endtime'])));
            }
        }
        $manageList = db('crm_manage')->field('id,username,name,fenzu_name')->where($where)->order('zu_id ASC,id ASC')->select();
        foreach ($manageList as $key => $value) {
            $where1['manage_id'] = $value['username'];
            $manageList[$key]['customer_number'] = db('crm_customer')->where($where1)->count();
            if (empty($manageList[$key]['customer_number'])) {
                unset($manageList[$key]);
            }
        }
        $list = [];
        foreach ($manageList as $key => $value) {
            $list[] = $value;
        }

        apiReturn(200,'获取工号客户数统计成功',$list);
    }

    /*
     *客户复购率统计
     *  */
    public function fugouCount()
    {
        $data = input();//token

        $list = array(array('id'=>0),array('id'=>1),array('id'=>2),array('id'=>3),array('id'=>4),array('id'=>5));
        foreach ($list as $key => $value) {
            if ($value['id'] <= 5) {
                $where['fugou_number'] = $value['id'];
            }else{
                $where['fugou_number'] = array('egt',5);
            }
            $list[$key]['customer_number'] = db('crm_customer')->where($where)->count();
        }

        apiReturn(200,'获取客户复购率统计成功',$list);
    }

    /*
     *客户复购率统计1
     *  */
    public function fugouCount111()
    {
        $data = input();//token

        $where['order_status'] = ['in','2,3,4,5'];
        $where['total'] = ['>',1000];

        $count1 = db('crm_order')->where($where)->group('customer_id')->having('count(customer_id)=1')->count();

        $count2 = db('crm_order')->where($where)->group('customer_id')->having('count(customer_id)=2')->count();

        $count3 = db('crm_order')->where($where)->group('customer_id')->having('count(customer_id)=3')->count();

        $count4 = db('crm_order')->where($where)->group('customer_id')->having('count(customer_id)=4')->count();

        $list = array(array('id'=>0,'fugou_number'=>$count1),array('id'=>1,'fugou_number'=>$count2),array('id'=>2,'fugou_number'=>$count3),array('id'=>3,'fugou_number'=>$count4));

        apiReturn(200,'获取成功',$list);
    }

    /*
     *通话记录统计
     *  */
    public function phoneNumberCount()
    {
        $data = input();//token zu_id starttime endtime

        if (isset($data['zu_id'])) {
            if (!empty($data['zu_id'])) {
                $wherezu_id = $this->getFenzuList($data['zu_id']);
                $where['manage_id'] = $this->getManageList($wherezu_id);
            }
        }
        if (isset($data['starttime']) && isset($data['endtime'])) {
            if (!empty($data['starttime']) && !empty($data['endtime'])) {
                $where['date'] = array(array('egt',strtotime($data['starttime'])),array('elt',strtotime($data['endtime'])));
                $where1['date'] = array(array('egt',strtotime($data['starttime'])),array('elt',strtotime($data['endtime'])));
            }else{
                apiReturn(201,'时间不能为空');
            }
        }else{
            apiReturn(201,'时间不能为空');
        }
        $where['manage_type'] = 3;
        $list = db('crm_phone_number')->field('manage_id')->distinct(true)->where($where)->select();

        foreach ($list as $key => $value) {
            $where1['manage_id'] = $value['manage_id'];
            $list[$key]['count'] = db('crm_phone_number')->where($where1)->sum('number');
            $manageInfo = db('crm_manage')->field('name,fenzu_name')->where('username',$value['manage_id'])->find();
            if (!empty($manageInfo)) {
                $list[$key]['name'] = $manageInfo['name'];
                $list[$key]['fenzu_name'] = $manageInfo['fenzu_name'];
            }else{
                $list[$key]['name'] = '无信息';
                $list[$key]['fenzu_name'] = '无信息';
            }
        }
        apiReturn(200,'获取成功',$list);
    }

    /*
     *回访周期报表
     *  */
    public function huifangCount()
    {
        $data = input();

        $follow_array = ['普通通话回访','开课电话','小灶课','检测电话','续费跟进通话'];
        $where['banzhuren_manage_id'] = ['neq','null'];
        if (isset($data['course_id'])) {
            if (!empty($data['course_id'])) {
                $where['formali_yingqi'] = $data['course_id'];
            }else{
                apiReturn(200,'获取成功',[]);
            }
        }else{
            apiReturn(200,'获取成功',[]);
        }

        $banzhuren_list = db('crm_customer')->alias('a')->join('tp_crm_manage b','a.banzhuren_manage_id=b.username')->distinct(true)->field('a.banzhuren_manage_id,b.name as manage_name')->where($where)->select();
        foreach ($banzhuren_list as $key => $value) {
            $where['banzhuren_manage_id'] = $value['banzhuren_manage_id'];
            $huifang_list = db('crm_customer')->field('client_id,banzhuren_manage_id')->where($where)->select();
            // $banzhuren_list[$key]['manage_name'] = db('crm_manage')->where('username',$value['banzhuren_manage_id'])->value('name');
            $banzhuren_list[$key]['daye15'] = 0;
            $banzhuren_list[$key]['daygt15'] = 0;
            foreach ($huifang_list as $key1 => $value1) {
                $huifang_genjin_date = db('crm_customer_follow')->where(['client_id'=>$value1['client_id'],'follow_type'=>['in',implode(',', $follow_array)]])->order('date DESC')->value('date');
                if ($value['banzhuren_manage_id'] == $value1['banzhuren_manage_id']) {
                    if (!empty($huifang_genjin_date)) {
                        $genjin_days = ceil((time()-intval($huifang_genjin_date))/(3600*24))-1;
                        if ($genjin_days == 15) {
                            $banzhuren_list[$key]['daye15']++;
                        }
                        if ($genjin_days > 15) {
                            $banzhuren_list[$key]['daygt15']++;
                        }
                    }
                }
            }
        }
        apiReturn(200,'获取成功',$banzhuren_list);
    }

    /*
     *学管续课报表
     *  */
    public function xueguanXuKeCount()
    {
        $data = input();
        $where['banzhuren_manage_id'] = ['neq','null'];
        if (isset($data['formali_yingqi'])) {
            if (!empty($data['formali_yingqi'])) {
                $where['formali_yingqi'] = $data['formali_yingqi'];
            }else{
                apiReturn(200,'获取成功',[]);
            }
        }else{
            apiReturn(200,'获取成功',[]);
        }
        $banzhuren = db('crm_customer')->field('banzhuren_manage_id')->distinct(true)->where($where)->select();
        $where1 = $where;
        $where2 = $where;

        foreach ($banzhuren as $key => $value) {
            $manageInfo = db('crm_manage')->alias('a')->join('tp_crm_fenzu b','a.zu_id=b.id')->field('a.name,a.type,b.name as fenzu_name')->where('a.username',$value['banzhuren_manage_id'])->find();
            $banzhuren[$key]['name'] = $manageInfo['name'];
            $banzhuren[$key]['fenzu_name'] = $manageInfo['fenzu_name'];

            $where1['banzhuren_manage_id'] = $value['banzhuren_manage_id'];
            $banzhuren[$key]['customer_number'] = db('crm_customer')->where($where1)->count();
            if (empty($banzhuren[$key]['customer_number'])) {
                $banzhuren[$key]['customer_number'] = 0;
            }

            $where2['xuke_label'] = 1;
            $where2['banzhuren_manage_id'] = $value['banzhuren_manage_id'];
            $banzhuren[$key]['xuke_number'] = db('crm_customer')->where($where2)->count();
            if (empty($banzhuren[$key]['xuke_number'])) {
                $banzhuren[$key]['xuke_number'] = 0;
            }
            
            if (!empty($banzhuren[$key]['customer_number'])) {
                $banzhuren[$key]['xuke_rate'] = sprintf("%.4f",$banzhuren[$key]['xuke_number']/$banzhuren[$key]['customer_number'])*100;
            }else{
                $banzhuren[$key]['xuke_rate'] = 0;
            }
            $banzhuren[$key]['mubiao_number'] = 0;
            $banzhuren[$key]['mubiao_rate'] = 0;
        }
        apiReturn(200,'获取成功',$banzhuren);

    }

}
